During this week, something I was working on required me to compare a current month figure to the average of either 3 or 6 months. This is specifically useful in situations where values may fluctuate, and it is more beneficial to get an idea of how a value compares to a figure that allows us to see the general trend, instead of an unrepresentative anomaly. In this blog, I'll show you how to use either a Table Calculation or a Level of Detail expression to achieve this.
The problem
Imagine you are working with highly volatile data, where certain fluctuations in values are expected. Maybe you are reporting on October progress, but the value in September is notably high because the company always receives the most income on that month for a specific reason. Would it be useful comparing the October value to this incredibly high spike? Or would it be better to compare the value to an average across N months?
Using Superstore as an example, lets imagine we are comparing December sales to November.
There's a huge spike in November sales, but we can also see that December sales are generally trending upwards, however they have not quite reached the expectations of November sales.
Ideally, we may want to get a better representation of performance. We can use a parameter in Tableau to achieve this, either in a LOD or a Table Calculation.
Table Calculation - LAST()
This table above shows us the months, the values from the last() function, and finally sales. We want to report on the average across the past N months (lets say 3 here), meaning we want to return values September, October and November. We can do this with the following calculation- we will be using a parameter which is just an integer, allowing the user to choose how many values they would like to consider in their calculation.
Notice how the following calculation returns these three values without returning December values. As long as the date is in the view, and it is truncated to the monthly level, this calculation will work when configured (tick the date field).
We can now use a window average to return the average of these values, and this can be used in a title if you'd like to incorporate it into a KPI card.
We now this value across the entire view, meaning it can be used for a KPI value in the title!
Level of Detail
This method is slightly trickier. It took a while to get this one. There is two elements to this calculation, as we need to replicate the last() function.
Lets break this calculation down into the two steps:
IF datetrunc('month', [Order Date]) >= dateadd('month', -[N Months], {max(datetrunc('month', [Order Date]))})
I would read this in the following format:
- From the current month ( our {max(datetrunc('month', [Order Date]))} calculation)
- Is the month of order date bigger than or equal to N months before our current month? (N=3).
If we just kept it like this, it would also return December 2020, since that date is bigger than September. So we need to exclude December!
datetrunc('month', [Order Date]) <= DATEADD('month', -1, {max(datetrunc('month', [Order Date]))})
- From the current month, subtract 1 from this value (ie, November 2020).
- Is our month less than or equal to this? (Hence, excluding December 2020)
As we can see above, we now get 3 values, which we need!
Now, lets get current month sales:
Next, lets calculate the average of those past 3 month sales.
All we are doing here is adding those three values together, and taking the window_avg() of that.
We now have a method of comparing our current month value to this 3 month average. We can now get the Percent difference of these two values and see how December 2020 has compared to the average of September, October, and November.
We need to use the window_sum(sum([Current Month Sales])) because we only have a value for the current month, but we need to be able to compare it across the entire table IF we want to use this in the title of our worksheet (which I consider the default).
You should get zero appearing, but all you need to do is format this value as a percentage, then it will show up!
I definitely prefer building out calculations in this tabular view before implementing them on a graph. I hope this helps to understand how we can dynamically compare a value to an average of multiple months.