4 Replies Latest reply: Mar 8, 2017 11:27 AM by Aar Kay RSS

    Fiscal Year definition

    Mike Sawyer

      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:

      Capture.JPG

       

      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. 

        • Re: Fiscal Year definition
          Aar Kay

          try this

           

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

          • Re: Fiscal Year definition
            Jens Leysen

            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.