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

# MTD and YTD calculations

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

• ###### 1. Re: MTD and YTD calculations

It appears that yesterday is going to be the latest date in your dataset. In which case instead of basing your DATEDIFF on today you could base it on yesterday, which should solve your problem. For example:

MTD calc:

If [Date Notified] <= TODAY()

Then 1

End

Interested to hear if that modification fixes this for you. If so you can modify YTD in the same way.

1 of 1 people found this helpful
• ###### 2. Re: MTD and YTD calculations

Thanks Andrew,

That seem very logical, I'll give it a go and will find out if it works on the 1st.

• ###### 3. Re: MTD and YTD calculations

Hi Alix,