To do multi-staged calculation, it is always easier to setup a table view first to check the numbers are correct for the calculated field, then produce the final view. So we will use Superstore data to demonstrate the method.
Note: Filter on year to 2021 is not necessary here, only reduce rows for screen shots. No effect on the calculation.
For future proof, it is always useful to find the max (latest) date, then us it as a reference date to do all the calculations. Here we used latest month, but any date interval is equally possible, or we can even make it dynamic through a parameter.
From the reference date, we can use it as a filter to select Sales for the latest month.
Note: Due to calculation steps later, it is more convenient to use non-aggregated [Sales] instead of SUM[Sales].
Then with a similar setup, we can find the previous 3 months values. We could also do more period here with a parameter to change the numbers to previous N months value.
We can now drag all the calculated fields into the following view to see they selected the correct numbers.
As the values are correctly selected, next stage is to find the average sales for the previous 3 months. We need to do it for each Category, so this indicate we need to use LOD (Fixed).
Note: 3 can be replaced by a parameter as mentioned before for more dynamic control.
We can see the value is constant throughout the month and it is the average of previous 3 months. We notice the latest Sales for Furniture and Supplies is higher than the previous 3 months average, while Technology is not.
Once we have the average sales value, we can do various calculation, e.g. difference, percentage difference etc. Here we will just do a simple boolean comparison to create an indicator.
By dragging the indicator into the view, we can see it is picking out the correct entries
As we see all the calculation is correct in the table view, we are ready to build our chart. In a new sheet we can drag all the labels into following view to create a bar chart (format is optional)
Looking for more guides, tips and tricks in Tableau or Alteryx? Go check out the other blog posts from the Data School.
