6 Replies Latest reply on Nov 7, 2014 9:23 AM by hiwot weldemariam

    How do I modify DATETRUNC calcs to use financial year instead of calendar year?

    Craig Dewar


      I am a fan of using  'reporting period' and 'no of trending periods' parameters in preference to relative date filter or a fixed range filter.  It works across mutiple data sources, allows users much more flexability, and is good for published workbooks that have more and more data added over time - without need for filter adjustment and republish.

       

      I have a solution that works quite well when periods are months or quarters, but i have just now tried to use my solution on a year period - where the year is a financial(fiscal) year starting july-1.

       

      Attached is an implementation of this solution on superstore sales, and all works as expected if year is calendar year:

       

      fiscal year 1.png

      However the solution fails when changing order date to be fiscal year commencing on Jul-1.  This is because the DATETRUNC function I am using is hard wired to 1-Jan of the year.

       

      fiscal year 2.png

       

      I have had a go a trying to shift my logic by 6 months without any success.

       

      Have attached a twbx

       

      Hope someone can help.