Here’s another way to make month over month Tableau comparisons or more broadly period over period comparisons. If you’re curious about other ways to make period over period comparisons, I recommend this post and this post. This one is more restrictive than some of the others I’ve done. But sometimes users need to be restricted. I like this method the best, as it’s very straight forward and intuitive if you’re using it for bar graphs and KPIs. Unfortunately, because of the lack of sophistication of the tableau programming language, it’s not the prettiest method. Nonetheless, I like it.
As usual, I’m using the superstore data set for this example. The method relies on three custom measures and a parameter: Current Period Profit, Double Period Profits, Previous Period Profits, and Days Back. Profit is one of the measures that comes with the superstore data set. All of these measures are easy to make.
The Parameter – Days Back
Make a parameter called “Days Back”. Make sure the data type is an integer. It’s tempting to make it a date, but that’s not what we want. there’s a lot of freedom with this parameter. You can range-bound it or not. Furthermore, if you know exactly the amount of time you need to look back. For example, if you always want to compare the current two weeks with the previous two weeks, I would recommend just hard coding “14” into the custom measures. You have flexibility here is my point. The magic of this method is in the custom measures, not the parameter. If you want to learn more about parameters, I recommend the Tableau article.
Current Period Profit
IF [Order Date] > ({MAX([Order Date])} - [Days Back]) THEN [Profit]
END
This custom measure is pretty straight forward. Using the order data filter it takes the most recent, or “MAX DATE,” and subtracts it from a certain number of days back. This creates a subset of dates that is the highest date available from the date filter to the number of days back.
Double Period Profits
IF [Order Date] > ({MAX([Order Date])} - ([Days Back]*2)) THEN [Profit]
END
This measure is where I gripe about Tableau’s lousy programming language. If only we could create variables. This custom measure will never be used on the worksheet, we simply need a place to store data that is from the current, or “MAX DATE,” until a point in time that is twice as far as the current period. As you can see, this custom measure is the same as Current Period Profit except it doubles the [Days Back].
Previous Period Profits
SUM([Double Period Profits]) - SUM([Current Profit])
If you subtract the Current Period Profits from the time period that’s twice as long, Double Period Profits, then the remainder is Previous Period Profits.
Putting It All Together
Now we add everything to the worksheet and we should be good. This method is best for KPIs. So throw all Current Period Profits and Previous Period Profits in the label field and customize how you please.
And that’s how you make a yet another month over month Tableau comparison.
A Few Recommendations
I recommend using the “Ending Date” style of filter. If the filter is smaller than the range of Double Period Profits, then you’re going to have issues with the custom measures.
This method also works well with bar charts and bullet graphs. I don’t recommend it for time series. The logic just isn’t built in such a way that it won’t work.
