'Month on Month' and 'Year on Year'
“Can I Compare the month to date to last month to date, oh and also between this year and last year”
This was the fairly innocent question that I got from a client the other day. Now it’s a fairly sensible question, though worded slightly awkwardly. What it's trying to ask is actually two questions.
What was my performance this month to date, compared to last month
What was my performance over the same period last year.
When we look over these at first they appear quite simple, but if you know a little about Tableau’s date filters then you know it isn't quite as simple as it first appears, especially when you throw in the year on year comparison.
For example, here we have the sales over the last few years for our favourite superstore:
When we apply Tableau’s date filtering we get some options, so we select the last two months (not yet seeing the trap we have laid down for ourselves:
Giving us this view:
Great so let’s break this down by year by dragging the Year of Order Date onto the colour shelf;
Oh, that's not helpful, even more unhelpfully we have the last month to date (the last month is in theory the current month and so aside from at close of business on one day of the month it is always going to be incomplete), and the previous month to the end of the month. It’s really all a mess.
So to fix it we are going to have to create our own way of filtering, using our old friend the calculated field:
So here we do a check on the month of the order date to see if it is the same as the month of the latest date (this latest date field could could be a fixed date, a calculation, or a parameter) We also have a special case when it's january, because rather than month 0 being the month before january its month 12 so we have to do a special check here.
Now if we remove our non functioning filter and add on our shiny new one we get this….
(for the filter we select all those that are true)
Which is closer, now we can see November and December for each year but we only want the last two years. Now we could use the relative date filter to get the most recent two years, but we are going to run into issues with jan/dec again so rather than use the normal filter for the last two years I'm going to write my own to cover the edge case.
Now it might seem silly to be covering for an edge case like this, but if this is a commonly used report it's going to be important to make sure it's as complete and accurate as possible so you need to cover this, else when January rolls around you're going to be in some hot water.
So to use this we do the following, we place it first on the filter shelf and exclude null, then we place a copy on the colour shelf, taking care to remove year from the rows or column shelf if it's there.
So here I have made it the running sum of sales to make it a little clearer.
The final part of this is to make it to date, now because the last datapoint in the dataset is the end of december i’m going to change latest date to a parameter so we can write and test the to date part of the filtering
This is another calculation, but much simpler this time, there are no obvious edge cases that i’m going to plan for (you could plan for the different month lengths, but i’m not going to do that here.
So now we have three calculations that are going to work together to answer the original questions
Last Two Months: This filters out all those months that are not the month of the latest date, or the month before. This also takes into account the wrap-around that happens at the beginning of the year
Last Two Years: This gives us the last two years as a dimension we can apply to the colour shelf and allows us to filter out dates that are not in the last two years.
To Date: Gives us only those days that are equal to or before the current day.
So when we apply all of these we get the following
So we can see the sum of sales for the month to date, against the same month last year, and the previous month, this year and last year.
It's a bit of added complexity but it’s worth the effort to get a more accurate view of the data.
Hopefully you have found this useful, please let us know in the comments if you have any questions, below is a link where you will be able to download the workbook to have a closer look at any of the calculations I have used.