When using a set of dated transactions which gets updated, how do you provide a report which will track visually a monthly YoY (Year over Year) YTD (Year to Date) comparison? This needs to work at the day level, to avoid comparing June 1 attainment this year to all of June the year before…
It would be easy to build a table in Excel, massage it until you get a side by side. But then, so much for automation! And it is actually faster to obtain in Tableau, as long as you have the underlying transactions with dates. Yet, in Tableau, it is not as easy as it seems, and there are quite a few people who have been looking into that issue. Since I struggled with table calcs which behave quite finicky for that task, I came up with a solution that will work for any calendar dimension (Day, Week, Month, Quarter). Don’t expect anything revolutionary here, but a nice handy trick, much easier imho to understand and deploy than Tableau’s official guidance.
Let’s use the good old Superstore data set to display monthly sales by Order date. Since the data set covers 2011 and 2014, for the sake of illustration, I will pretend today is June 15, 2014. You should reproduce the following steps on your own live data set to use YTD filter, as Superstore is not current date wise.
- Select the relative last 2 years:
and then use Month Discrete as a pill followed by Year, and get something like this:
- The challenge is that I don’t want the remaining months of the previous year, that is 2013 in my illustration:
Thus, I will create a filter to remove that time frame from my viz:
Here it is ready to copy & paste:
// Select YTD
// Select previous year YTD
[Order Date]<=dateadd(‘year’, -1,today())
- I suggest you remove 0 from axis for a more visual comparison, by unchecking the default box in axis properties:
- Final results upon setting Filter Rest of Year to TRUE:
And it will work automatically from there on, fire & forget!