1 Reply Latest reply on Jan 12, 2018 9:39 AM by Jim Dehner

    Fiscal Year Statement

    jeff.aldridge

      I have created a fiscal year statement that reads "Net Employees between 1/1/2017 and 12/31/2017". I have created the following formulas below for week start and week end. I want to make the "week start" at the beginning of the fiscal year (October) as opposed to calendar year. The "week end" is on the date I want but I am having issues making the "week start" begin on October 1st. Any assistance would be greatly appreciated.

       

      Week Start

      IF [Date Rollup] = "year" THEN

      IF MONTH(TODAY()) = 10

      THEN

      DATE(DATETRUNC('month',TODAY()))

      ELSEIF

      MONTH(TODAY()) = 11

      THEN

      DATE(DATETRUNC('month',DATEADD('month',-1,TODAY())))

      ELSEIF

      MONTH(TODAY()) = 12

      THEN

      DATE(DATETRUNC('month',DATEADD('month',-2,TODAY())))

      ELSE

      DATE(DATETRUNC([Date Rollup],

      DATEADD([Date Rollup],[Date Interval], TODAY())))

      END

      ELSE

      DATE(DATETRUNC([Date Rollup],

      DATEADD([Date Rollup],[Date Interval], TODAY())))

      END

       

      Week End

      IF [Date Rollup] = "month" THEN

      DATE(DATEADD('day',-1,DATETRUNC('month',TODAY())))

      ELSE

      DATE(DATEADD('day',-1,

      DATETRUNC('week',DATEADD('week',-1, TODAY()),'sunday')))

      END

        • 1. Re: Fiscal Year Statement
          Jim Dehner

          Hi Jeff

          Not sure I fully understand what you are doing but there may be an easier way - If I understand this right you want to be able to use Calendar year or Fiscal year at different points

           

          you can make a copy of your day set the default date property on the copy to start the FY in October

           

          Now each Calendar date is associated with a Fiscal date  --

           

          it would return something like this

           

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.