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

This boolean formula will calculate “This Year”:

YEAR([DateField]) = YEAR(TODAY())

Sales this year, as an example, and using the above formula as the [IsThisYear] calculation, is:

SUM([Sales])*INT([IsThisYear])

Create a similar boolean calculation for “Last Year”:

YEAR([DateField]) = YEAR(DATEADD('year',-1,TODAY()))

And use the same way to calculate sales last year:

SUM([Sales])*INT([IsLastYear])

Use the “sales this year” and “sales last year” calculations to calculate YoY change using boolean calculations.

14 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
  3. I have only year field for students in take. The issue is to calculate retention rate for each academic year, I should divide the current year student by previous year intake i.e. COUNT(overall)
    How to set up this formula?

    Reply
    • Retention rate would generally be calculated at the level of the individual – however, for the academic world this might be different, given those in the final year are expected to leave. Whether possible at the student level also depends on the availability of that data. Usually you would count the students of last year (the retention opportunities) and see which of those are still students this year (retained opportunities). Your retention rate is SUM(Retained)/SUM(Opportunities).

      Of course, you could also divide the count of students this year by the count of students last year. It’s not really a retention rate, but will show the YoY change. This article shows how to do that, but if you want to see more detail on the Boolean option you can also read here: https://tarsolutions.co.uk/blog/tableau-boolean-data-type/#yoy

      Reply
  4. YoY Change shows NULL for my data. This Year and Last Year calculations work just fine. When I move YoY Change to the Rows column it shows “AGG(YoY Change)”

    Reply
    • Make sure you haven’t filtered any of the years out of the data. Both years are necessary for the calculation

      Reply
        • Use the calculated fields option, which requires 3 calculated fields. One to calculate “this year”, another for “last year”, and a 3rd to calculate the % difference between those 2 fields – the YoY change. Don’t filter out “last year” from the underlying data as you need this for the calculation to work. That doesn’t mean you need to display it – use the “this year” calculation to display the current year numbers.

          Reply
  5. hello there and thank you for your information. I have definitely learn something new from your article here.

    Reply

Leave a Comment