As mentioned in my previous post, in order to show how a business is performing, it is good to do a year on year comparison. This allows for a quick overview to see if the values are increasing or decreasing.

Previously, I've shown how to do YoY calculations with table calculations, this time I will show you how to do them with LODs ( level of detail expressions ). These epxressions are denoted by mustachios {}. They end up fixing on a particular part in the data and can not be as easily influenced as table calculations.

Therefore, to start building our YoY comparison we need to first build out our Current Year (CY) and Previous Year (PY)

To do this we have to fix on YEAR of Order date, find out the max value of it and to return only that max value. The calculation would look something like this. The mustachios on the outside end up fixing it and forcing it to be returned for all rows. Same is done with PY with the minor difference that we have -1 on the outside of LOD.

We can see that for CY and PY it returns the value we want, this can be tested in a cross-tab in tableau.

It returns 2022 for CY and 2021 for PY. This lets us to keep on building out the logic.

Therefore to return the sales for CY we have to build another calculation.

This basically compares the year of order date with our previously built out CY and returns sales, same is done for PY sales just instead of CY we use PY.

The only thing is this though, we run into a small problem. Because of the way the calculation is built out it only returns that specific value and every other row is going to be empty

In order to fix this we need to create another calculation which would fix onto the returned value. This is done by simply using FIXED LOD

Returning us the sum of sales from the CY, in the table it fills out other rows with the same value.

As shown in the example above. The same is done with PY sales to return the value across all rows.

By putting the Year on filter and filtering out all the values, we can still return the previous and current sales, this also allows us to do a % difference between them.

To make the % difference calculation we simply use a (CY-PY)/PY. All of these are sales:

Don't forget to right click the calculation when it is in the view and format it, and make it a percentage, this will let you return the % difference from previous year, and will always update as new data comes in.

Thank you for reading :)