Year-over-year comparison in Tableau - Step-by-Step Guide

Introduction

Year-over-year comparison helps you see how a number changes from one year to the next. It is useful to understand growth or decline over time, like comparing sales this year to last year.

When you want to compare this year’s sales to last year’s sales to check growth.

When you need to show how website visitors changed from one year to the next.

When your manager asks if profits improved compared to the previous year.

When you want to highlight seasonal trends by comparing the same month in different years.

When you want to track yearly changes in customer counts or product orders.

Steps

Step 1: Open your Tableau workbook

- Tableau Desktop main window

Your data and sheets are visible for analysis

Step 2: Drag the date field to the Columns shelf

- Columns shelf

Dates appear as a timeline across the top of the view

Step 3: Right-click the date field on Columns shelf and select 'Exact Date', then right-click again and choose 'Discrete'

- Columns shelf date field context menu

Dates show as individual years on the axis

Step 4: Drag the measure you want to compare (e.g., Sales) to the Rows shelf

- Rows shelf

A bar or line chart shows the measure values by year

Step 5: Create a calculated field for Year-over-Year change

- Data pane → right-click → Create Calculated Field

A new field window opens to enter the formula

💡 Use the formula: (SUM([Measure]) - LOOKUP(SUM([Measure]), -1)) / LOOKUP(SUM([Measure]), -1)

Step 6: Name the calculated field 'YoY Change' and click OK

- Calculated Field dialog

The new YoY Change field appears in the Data pane

Step 7: Drag the 'YoY Change' field to the Rows shelf next to your measure or to the Tooltip shelf

- Rows shelf or Tooltip shelf

The view updates to show year-over-year change values or shows them when hovering over data points

Before vs After

Before

The chart shows total sales for each year: 2019 = 100,000; 2020 = 110,000; 2021 = 105,000

After

The chart shows total sales and year-over-year change: 2020 = +10%, 2021 = -4.5%

Settings Reference

Date field type

📍 Columns shelf → right-click date field

Controls how dates are grouped and displayed for comparison

Default: Year (Discrete)

Calculation formula

📍 Calculated Field dialog

Defines how year-over-year change is calculated

Default: None

Measure aggregation

📍 Rows shelf → measure field

Determines how data values are summarized for each year

Default: SUM

Common Mistakes

Using continuous date instead of discrete year

Continuous dates show a timeline that can confuse year-over-year comparisons

Use discrete year to clearly separate each year on the axis

Not using LOOKUP function in the calculation

Without LOOKUP, the calculation cannot compare current year to previous year

Use LOOKUP(SUM([Measure]), -1) to get the previous year's value

Placing the YoY calculation on the wrong shelf

If placed incorrectly, the calculation may not display or update properly

Place the YoY Change field on Rows shelf or Tooltip shelf for correct visualization

Summary

Year-over-year comparison shows how a measure changes from one year to the next.

Use discrete year dates and a calculated field with LOOKUP to create the comparison.

Place the YoY calculation on the view to see percentage changes clearly.

Practice

1. What is the main purpose of a year-over-year comparison in Tableau?

easy

A. To compare values from one year to the previous year

B. To calculate the total sales for a single year

C. To display data only for the current year

D. To filter data by month

Solution

1. Step 1: Understand year-over-year comparison

   Year-over-year comparison is used to see how a value changes from one year to the next.

2. Step 2: Identify the correct purpose

   Comparing values from one year to the previous year matches the definition of year-over-year comparison.

3. Final Answer:

   To compare values from one year to the previous year -> Option A

4. Quick Check:

   Year-over-year = compare year to previous year [OK]

Hint: Year-over-year means comparing this year to last year [OK]

Common Mistakes:

• Confusing year-over-year with total yearly sales
• Thinking it filters data instead of comparing years
• Assuming it only shows current year data

2. Which Tableau function is commonly used to get the previous year's value for year-over-year calculations?

easy

A. SUM()

B. WINDOW_SUM()

C. DATEPART()

D. LOOKUP()

Solution

1. Step 1: Identify function for previous value

   LOOKUP() function in Tableau returns a value from a previous or next row, useful for previous year values.

2. Step 2: Confirm correct function

   WINDOW_SUM() sums over a window, DATEPART() extracts date parts, SUM() totals values, but only LOOKUP() fetches previous year value directly.

3. Final Answer:

   LOOKUP() -> Option D

4. Quick Check:

   Previous year value = LOOKUP() [OK]

Hint: LOOKUP() fetches previous row values, perfect for last year [OK]

Common Mistakes:

• Using SUM() instead of LOOKUP() for previous year
• Confusing DATEPART() with fetching previous values
• Using WINDOW_SUM() which sums but doesn't get previous year

3. Given this Tableau calculation for year-over-year growth:
SUM([Sales]) - LOOKUP(SUM([Sales]), -1)
What does this calculation return?

medium

A. The percentage growth of sales year-over-year

B. The difference in sales between the current year and the previous year

C. The sales for the previous year only

D. The total sales for the current year

Solution

1. Step 1: Analyze the calculation components

   SUM([Sales]) gives current year sales; LOOKUP(SUM([Sales]), -1) fetches previous year sales.

2. Step 2: Understand the subtraction

   Subtracting previous year sales from current year sales gives the difference in sales year-over-year.

3. Final Answer:

   The difference in sales between the current year and the previous year -> Option B

4. Quick Check:

   Current year sales - previous year sales = difference [OK]

Hint: Subtract LOOKUP() from current sum for difference [OK]

Common Mistakes:

• Thinking it calculates percentage growth
• Assuming it returns only previous year sales
• Confusing it with total sales calculation

4. You created a year-over-year calculation using LOOKUP but the results are incorrect. What is the most likely cause?

medium

A. The calculation uses SUM instead of AVG

B. The data source is missing the Sales field

C. The table calculation direction is not set to compute using Year

D. The filter is applied after the calculation

Solution

1. Step 1: Identify common error in LOOKUP calculations

   LOOKUP depends on table calculation direction; if not set to Year, it fetches wrong rows.

2. Step 2: Confirm why direction matters

   Setting compute using Year ensures LOOKUP moves along years, giving correct previous year values.

3. Final Answer:

   The table calculation direction is not set to compute using Year -> Option C

4. Quick Check:

   Wrong direction = wrong previous year value [OK]

Hint: Always set table calc direction to Year for LOOKUP [OK]

Common Mistakes:

• Ignoring table calculation direction
• Changing aggregation from SUM to AVG unnecessarily
• Assuming missing fields cause LOOKUP errors

5. You want to create a dashboard showing year-over-year sales growth percentage. Which calculation correctly computes this in Tableau?

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

hard

A. This calculation will cause a division by zero error if previous year sales are zero

B. This calculation should use WINDOW_SUM instead of SUM

C. This calculation correctly computes the year-over-year growth percentage

D. This calculation needs to add 1 before multiplying by 100

Solution

1. Step 1: Understand the calculation formula

   The formula calculates difference divided by previous year sales, then multiplies by 100 for percentage.

2. Step 2: Identify potential issue

   If previous year sales are zero, division by zero occurs causing error or infinite result.

3. Step 3: Evaluate other options

   WINDOW_SUM is not needed here; adding 1 is incorrect for percentage growth calculation.

4. Final Answer:

   This calculation will cause a division by zero error if previous year sales are zero -> Option A

5. Quick Check:

   Division by zero risk if previous year sales = 0 [OK]

Hint: Check for zero in denominator to avoid errors [OK]

Common Mistakes:

• Ignoring division by zero possibility
• Replacing SUM with WINDOW_SUM unnecessarily
• Adding 1 incorrectly in percentage formula