2 Replies Latest reply on Aug 27, 2018 5:50 PM by Doug Stanley

    Comparing fiscal year to date to previous fiscal year to date

    Doug Stanley

      I'm embarrassed to say how many hours I've spent on this without success. Hoping someone can help.

       

      I need to compare fiscal YTD sales to the same period a year ago, i.e. previous fiscal YTD.

       

      My Date field is a calendar date. My fiscal year starts in July.

       

      I have tried using this calc to create a Fiscal Date that I can do calculations on:

      DATE(DATEADD('month',6,[Date]))

       

      I am using this calc to compute YTD Revenue:

       

      IF DATEPART('dayofyear',[FiscalDate]) >

         { FIXED : DATEPART('dayofyear',MAX([FiscalDate])) }

      THEN NULL

      ELSE [Revenue]

      END

       

      The combination of those two calcs produces this, which is ALMOST what I need:

       

      Despite the Fiscal Date calculation above, Tableau is recognizing January as the start of the fiscal year, not July.

       

      What am I missing?

       

      Also, bonus question, how can I filter out a partial month for THIS fiscal YTD and the same month during the PREVIOUS fiscal YTD?

       

      This gets me halfway there, filtering out the partial month in this fiscal year's data:

      DATETRUNC('month', [Date]) <> DATETRUNC('month',TODAY())

       

      How would I tell Tableau to exclude the same month a year earlier?

       

      Apologies for lack of a packaged workbook. My data is proprietary and creating a dummy data set would require more time than I have right now.

       

      Thanks,

      Doug