Calculating YoY of Quarterly and Yearly Sums in Tableau

Angel works in Finance. She often asks me questions on calculations in a table. Today I got this question: How to calculate Year over Year (YoY) change ratios for both quarterly and yearly sums, in a single sheet?

Here is the solution we got. First there are two parts for the YoY calculation.

For the quarterly YoY, we can apply this formula:

SUM([Sales])/LOOKUP(SUM([Sales]), -4) -1

For the yearly YoY, the above formula doesn't work. We need a slightly different one like:

SUM([Sales])/LOOKUP(SUM([Sales]), -1) -1

To combine them in a single one, we got this:

Note that CountD()=1 is equivalent of Max()=Min() when we deal with grand total/subtotal calculations.

Then the results are shown in this table:

The above formula works well logically. Here we have a few variants that look simpler.

They all work for YoY calculations. Pick the one you like if you need one.

Download the demo workbook here.