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
YEAR([Order Date])=year(today())
OR
// 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!
Hi – this does not work when the Calendar Year and Fiscal Year are not the same. In my data the FY starts in July. This approach results in only the months within the Calendar Year being displayed. I think its because the word ‘year’ in this statement [Posted Date]<=dateadd('year',-1,today()) – refers to calendar and not fiscal year?
Correct, if your Fiscal Year does not use calendar months, then Tableau does not manage it natively in its date functions and you have to take a different approach, depending on the type of Fiscal Calendar…
How can this be adjusted for weekday shift?
Hi – so to do the fiscal year … you can do something like this
// filter rest of year
YEAR([Fiscal Start Month])=YEAR(TODAY())
OR
[return_date]<=DATEADD('year', -1,TODAY())
// Fiscal Start Month – Fiscal Year begins in July
DATEADD('month',6, [return_date])
Hello,
Thank you for posting this. It is great! I have a question: How would I calculate YTD without showing the monthly split? For example, assuming the current month is August and YTD is through August, I want to compare 2020 YTD with 2019 YTD and so on. How would I do that? And how would I automate that so it updates every month. PS: There is a one month lag with data which means Aug data is available in Sept and so on.
Thanks!
Pretty simple, remove the Month pill from the query, and created a special filter to display only last closed month, by removing current month…
Thanks for this! Anyone know how to do this so it is always just a twelve month rolling and you don’t have to change anything when data is updated? For example, if the current month is September, then the chart would go from October to September?
Since the filter is relative, set it to pick the last 12 months…