In my last post, I outlined one method for creating last and prior calculations using Tableau’s LAST() function. Although this is a pretty straightforward and useful method, it limits you in terms of creating table calculations such as percent change. In this post, I will show you another way to create last and prior calculations that will also allow you to perform table calculations.
Using level of detail calculations and IIF statements
The first thing you want to do with this method is create a calculation that will give you the last, or most recent, date in your data set. Using the sample superstore data, this would be something simple like:
By wrapping the expression in curly brackets, we’re essentially creating a fixed level of detail calculation. But in this case, because we haven’t set the scope, we’ve told Tableau to calculate the figure based on our entire dataset, thus giving us our most recent date. This will help when we do the DATEDIFF calculation in the next step.
Now if we go back to the table we created previously using the LAST() calculation, we can see that the most recent month in our dataset is given a value of 0. Again, as before, our last 6 months would therefore be months 0-5 and our prior 6 months would be months 6-12.
Using this information and the most recent date (i.e. last date) calculation, we can use the DATEDIFF function to generate our last 6 months that looks like this:
And for prior 6 months:
When we drag them into our table, we create a Boolean True/False calculation for the months in our dataset
In order to get the actual figures, we need to create another two calculations using an IIF statement. Because we cannot wrap our Sales figure in a SUM aggregation INSIDE the IIF statement, we can work around this by wrapping the SUM aggregation around the outside of the IIF statement like so:
Calculation for last 6 month sales
Calculation for prior 6 month sales
And now we can create a dual axis chart as I did in my previous post that compares the two figures:
Now the last thing we want to do with this method that we weren’t able to do with the previous method is create a percent change calculation. The calculation is as follows:
And now if we put everything together in a table, we can see our final result with our percent change calculation. This can be used to display percent change for any of our dimensions and the figures will update accordingly: