Tableau Year on Year calculation

//

In Tableau there are two main methods for calculating a year over year change:

  1. YoY Growth Table Calculation method
  2. Using standard calculated fields to calculate Year on Year Growth

Year over Year Growth Table Calculation

First to look at the Table Calculation. If wanting to do year on year change on a line chart, Tableau makes it very simple. Use a built-in Quick Table Calculation.

Set up a line chart with time (months is what I’ll be using in this example as it’s the most commonly used and easy to visualise) on the X-axis and a measure, such as sales, on the Y-axis.

Drag the Year into the colour shelf so you have two years on the same graph.

You should have something like this with each line being a year:

year on year line chart in tableau

Tableau has an in-built set of table calculations, one of those being Year on Year Growth. To get the chart to show the growth instead of the actual numbers is very straightforward.

  1. Left click on the little arrow on the right of the green pill in the rows shelf.
  2. Go to Quick Table Calculation
  3. Click on Year Over Year Growth
Tableau menu showing Quick Table Calculation - Year over Year Growth

This will give you a single line showing the growth for each month. It’s as simple as that, a year on year calculation created in a few short steps.

Show the actual numbers for both years and the YoY growth % on the same chart is where Tableau comes into it’s own.

Note: when not at the end of the year, showing Year on Year change in a line chart can show a line at 0, when actually it is unknown, for example, the date hasn’t happened yet. It is simple to remove the Null values from the line chart using a filter.

Show the YoY Growth on the labels

As above, create the standard line chart without the YoY change.

Trend both years in the chart, as in the diagram.

Then also place SUM([Sales]) on the label shelf. To do this hold down the ctrl key and drag the green SUM([Sales]) pill from the Rows shelf into the Label shelf on the left of the screen.

Now the SUM([Sales]) is on both Rows and Label.

Now, with the SUM([Sales]) in the Label repeat the Quick Table Calculation to show the Year over Year Growth. Now you will have the actual data trended with the Year over Year Growth rates written at each data point.

Tableau line chart showing year on year with labels

Note: See the triangle in the SUM([Sales]) table calculation? That triangle means that pill is a table calculation.

Year over Year with calculated fields

If you prefer just to create a simple This Year / Last Year calculation we can use calculated fields. Staying with SUM([Sales as a measure]), three new calculated fields are needed.

1. Sales this year. A formula such as the following should work:

[Sales this year]: IF YEAR([DateField]) = YEAR(TODAY()) THEN [Sales] END

2. Sales last year:

[Sales last year]: IF YEAR([DateField]) = YEAR(DATEADD('year',-1,TODAY())) THEN [Sales] END

3. Year on Year Change:

[Year on Year Change]: SUM([Sales this year]) / SUM([Sales last year]) - 1

The most appropriate option all depends on the specifics of what you’re trying to do and also the shape of your data.

Simplify the Yearly calculations using boolean

It’s also possible to make intelligent use of the Boolean data type to identify the time periods, such as this year and last year. A full write up of this method is in the article giving an overview of the boolean data type.

These boolean fields offer the flexibility to calculate yearly data over a number of different measures without the need to repetitively write:

IF YEAR([DateField]) = YEAR(DATEADD('year',-1,TODAY())) THEN … END

Instead Sales This Year, as an example, would be:

SUM([Sales])*INT(IsThisYear)

7 thoughts on “Tableau Year on Year calculation”

  1. Is there a way to do something similar for MoM calculation? Haven’t seen that anywhere. Thanks.

    Reply
    • Without seeing your data I think you can try using a different quick table calculation. If you try Percent of Difference (or maybe Difference if you don’t want a % difference) and play around with the Compute Using and Relative To fields – which become visible once you’ve selected Percent of Difference as a quick table calc – you should be able to do a MoM without too much difficulty. Let me know if this doesn’t work for you and if not maybe I’ll be able to take a quick look at your data.

      Reply
  2. Hi all,

    Can u help me to find rest of the year balance(budgets). For example I am allocating budget $10 for all 12 months. During Jan my actual spent is $8. Remaining is $2. Now the budget for remaining month is $110 + $2 from Jan. I need to show the like feb remaining balance is $12 how to do that in calculated filed.

    Reply
    • Assuming the $10 is hardcoded into your underlying data you could use a LOOKUP function to check whether the previous value was 10 and if not use that value to adjust the 10. Further information about the LOOKUP is available on many blogs, this is a link to Interworks: https://www.interworks.com/blogs/tmccullough/2014/09/29/5-tableau-table-calculation-functions-you-need-know

      Your calculated field would be something such as [Budget] + IF LOOKUP([SPEND],-1) < [Budget] THEN [Budget] - LOOKUP([SPEND],-1) + [Budget] END That's checking if last months spend was less than budget and if so calculating the difference between spend and budget and adding it to the budget - this is assuming budget is fixed each month.

      Reply
    • Can you be more specific? If the post doesn’t cover how to do that your requirements must be non-standard. Thanks

      Reply

Leave a Comment