What I did in there:
I did a FIXED LOD to get the last date in the data source. (I displayed that in the header.)
Then I made a filter that gets months only up to the month of the last date in the data source. (Regardless of year.)
Now you don't need separate calcs for this year and last year.
Beautiful. This looks like it should work. I'll reply again after some testing.
Seems I spoke too soon. The original data ends on a date that is sometimes early in the month, sometimes late in the month, for example, 4/2/18. The method you've used would be great if it always ended on the same date at the end of the month, but since there is overlap (and I'm not privy to when/how that date is chosen) the method you've used would calculate the total for April 2017 and for April 2018, even though 2017 would have much more sales recorded since the whole month is in the data set, as opposed to 2018 where only two days of transactions are recorded.
Also, the reason that I was wanting to use two different calculations was because I also wanted to chart the YTD %Diff in sales as well, but this is secondary to getting the format correct to begin with.
Back to the drawing board.
1 of 1 people found this helpful
New strategy. Still using [Last Date in data]. Now I went with your YTD calc, and made a parallel PYTD (for previous YTD) calc.
The DATETRUNC function truncates a date to the beginning of whatever time period you specify. So in this case the Match 31 date gets truncated to Jan 1 of the year in question. So the YTD calc is pretty straightforward. Grab from Jan 1 through the last date.
In the PYTD I embedded a DATEADD function which adds whatever time period you specify. In this case I specified 'year', and I added -1.
So that calc grabs jan 1 of the prior year through whatever date was in the [Last] LOD calc of the prior year.
One caveat. Tableau's date functions are pretty powerful. It even handles leap years. But on the day that you have Feb 29 as your last date, I am not sure if you will get Feb 28 or March 1 as the end of range on your prior year. Tableau will handle it, but I'm not sure what it will do there, so if you ever will have that situation (in 2020), you might want to double check what you get from that.
see the attached - this is a brute force way - using the max date in the data set
It returns this for your data
here are the calculations involved (thy became more complex to get null values after the last month
and the running totals need to be set as shown
each is set independently
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.