Compare Latest Month with Previous Months Average in Tableau (LOD)

by Liu Zhang

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.

Starting Table View - 1

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.

Find latest date

From the reference date, we can use it as a filter to select Sales for the latest month.

Find latest date Sales

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.

Find previous 3 months Sales

We can now drag all the calculated fields into the following view to see they selected the correct numbers.

Table View - 2

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).

Finding average Sales

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.

Table View - 3

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.  

Compare sales values

By dragging the indicator into the view, we can see it is picking out the correct entries

Table View - 4

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)

Final

Looking for more guides, tips and tricks in Tableau or Alteryx? Go check out the other blog posts from the Data School.

Tableau Public

LinkedIn

Twitter