Use Tableau to Calculate Current & Prior Year Values Using Level of Detail Calculations

Use Tableau to Calculate Current and Prior Year Values Using Level of Detail Calculations

Contributed by: Lorna Brown

Difficulty level: Intermediate

When you are building Tableau dashboards, stakeholders might ask you to compare the Current Year values to Last Year values, which will allow the audience to see whether the current year is under or over performing compared to the previous year. This adds additional context and the “so what” to your current year values.

This Shortcut will allow you to find the maximum year in the dataset, followed by prior year, and finally to wrap that into bringing values back just for those years. By breaking down the calculations, it allows for user testing at every level. This Shortcut uses Level of Detail Calculations (LODs).

Finding the Maximum Year Using LODs

To start this Shortcut, open a new calculation window. This calculation will allow you to isolate the maximum year in the dataset you are using and it will be used in subsequent calculations to return the correct values:

Calculation: // Max Year

        { FIXED : MAX ( YEAR ( [Date Field] ) ) }

To check your calculation, bring the field on to the Rows shelf in Tableau Desktop; only one value should be shown.

Finding Prior Year

Now you have calculated your maximum year within your dataset. To return the prior year number you can subtract 1 from the maximum year:

Calculation: // Prior Year

        [Max Year]  –1

Max Year and Prior Year Values

Now that you have calculated your maximum and prior ...