4 Replies Latest reply on Sep 27, 2017 6:57 AM by Garrett Joeckel

    YTD Comparison Calculations accounting for Fiscal Year

    Garrett Joeckel

      Hello,

       

      I'm very close to getting my YTD Comparison Filter formula to work, in fact it does work but it is not taking in account my fiscal year start.

       

      Here is the calculation.

       

      IF DATEPART('dayofyear', [Ship Date]) <= DATEPART('dayofyear',TODAY())

      THEN 'YTD'

      ELSE 'FUTURE'

      END

       

      Pretty simple, and it works, but the system is defaulting to a year start of January 1 (despite the fact I have changed the fiscal year on the datasource to start in august).

      I need the 'dayofyear' count to start on August 1.  Any guidance anyone can give on what and where to imbed a dateadd function in the formula above or something else to make this work would be helpful, I've messed around with the dateadd function and continue to get errors.  Here's what the viz looks like now and you can see the data starts in January and ends in September, and is missing the values for October/November/December.

       

      The fiscal year in the viz above is a calculated field placing the numbers in the correct fiscal year designation, which is why the months start in August.

       

      Thanks,

      Garrett