3 Replies Latest reply on Nov 17, 2016 3:48 AM by Sreekanth Kasaraneni

    MTD and YTD calculations

    Alix Britt

      Hi

       

      I'm hoping someone on here might be able to help me with a calculation I'm struggling with.

      I'm fairly new to Tableau and haven't got my had around calculations so well yet.

       

      I have a daily dashboard that refreshes every morning to show daily, WTD, MTD and YTD volumes based on the previous days business.

      The issue I have is that on the 1st of every month the dashboard should be showing the figures as at the last day of the previous month (yesterday) but the MTD shows figures for the current month (today) ie the 1st.

      The same issue will occur on the 1st day of the new year for the YTD figures which I need the report to show the figures as at the last day of the year.

       

      I've got the calculated field to get the MTD and YTD figures (shown below) I just need some help to add in a bit to say if 1st day of month return last months volumes, and if 1st day of new year then return last years volumes.

      Note our business year runs from Oct to Sept

       

      MTD calc:

       

      If [Date Notified] <= TODAY()

      And DATEDIFF('month',[Date Notified],TODAY()) = 0

      Then 1

      End

       

      YTD Calc:

       

      If [Date Notified] >= DATE(STR(DATEPART('year',DATEADD('month',3,TODAY()))-1) + "-10-01")

      AND [Date Notified] <= TODAY()

      Then 1

      End

       

      Thanks

       

      Alix