Fiscal Year definition

[msawyercke]

Good Morning,

I'm having problems with Fiscal Year definition.

I have the following dates that define our Fiscal Year calendar over the next few years:

Given these dates, what would be an example of the expression that would define Fiscal Years in the autoCalendar section in my data load editor?

Many, many thanks to whomever can guide me in the correct direction. 

[aarkay29]

try this

if(TempDate > Weekstart(MonthEnd( Makedate(Year(TempDate),01))), Year(TempDate), Year(TempDate)-1) AS FiscalYear,

[msawyercke]

Thanks for your reply, Aar.  But, I don't understand where the 'begin' dates and 'end' dates are used in that expression...

Would you be so kind as to spell that out?

[Anonymous · Accepted solution]

Since your starting date and ending date vary each year, I would just put them in manually.

Try this:

IF(TempDate > 1/27/2015 AND TempDate < 1/25/2016 , 'FY2016',

IF(TempDate > 1/26/2016 AND TempDate < 1/30/2017 , 'FY2017',

IF(TempDate > 1/31/2017 AND TempDate < 1/29/2018 , 'FY2018',

IF(TempDate > 1/30/2018 AND TempDate < 1/28/2019 , 'FY2019',

IF(TempDate > 1/29/2019 AND TempDate < 1/27/2020 , 'FY2020',

IF(TempDate > 1/28/2020 AND TempDate < 1/25/2021 , 'FY2021',

IF(TempDate > 1/26/2021 AND TempDate < 1/31/2022 , 'FY2022', ))))))) AS [Fiscal Years]

Only thing here is that you should place the matching condition in the same format as your TempDate and after 2022 you will have to type the next years in the script manually again.

Let me know if this works.

[aarkay29]

Hi Mike,

     The above expression would work if you are using a Master Calendar.

Not sure if you can use this in auto calendar script.