Year-over-year growth in Power BI - Step-by-Step Guide

Introduction

Year-over-year growth shows how a value changes compared to the same time last year. It helps you see if sales, revenue, or other numbers are improving or declining over time.

When you want to compare this year's sales to last year's sales for the same month.

When your marketing team needs to see if website visits increased compared to last year.

When you track monthly revenue and want to highlight growth or decline from the previous year.

When you prepare a financial report showing yearly performance changes.

When you want to create a dashboard that updates automatically with new yearly data.

Steps

Step 1: Open your Power BI Desktop file

- Power BI Desktop main window

Your report and data model are visible on screen

Step 2: Click on the Modeling tab

- Ribbon at the top

Modeling options appear below the ribbon

Step 3: Click New measure

- Modeling tab

A formula bar appears to enter a DAX expression

Step 4: Type the following DAX formula for year-over-year growth: YoY Growth = DIVIDE(SUM(Sales[Amount]) - CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Date[Date])), CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Date[Date])))

- Formula bar

A new measure named YoY Growth is created in your model

Step 5: Add a visual like a card or line chart to your report

- Visualizations pane

The visual appears on the report canvas

Step 6: Drag the YoY Growth measure to the Values field of the visual

- Visualizations pane fields

The visual shows the year-over-year growth percentage

Step 7: Add a date field to the Axis or Legend of the visual

- Visualizations pane fields

The visual breaks down growth by year or month for comparison

Before vs After

Before

Report shows total sales by month without comparison to last year

After

Report shows year-over-year growth percentage for each month, highlighting increases or decreases

Settings Reference

New measure

📍 Modeling tab in Power BI Desktop

To write formulas that calculate year-over-year growth

Default: No measure created

Visual type

📍 Visualizations pane

Choose how to display the year-over-year growth data

Default: Card

Date field

📍 Fields pane

Used to compare current and previous year data

Default: Date

Common Mistakes

Using a date field without a proper date table

SAMEPERIODLASTYEAR requires a continuous date table to work correctly

Create or use a proper date table marked as a date table in Power BI

Not using DIVIDE function and dividing by zero

Direct division can cause errors if last year's value is zero

Use DIVIDE function which safely handles division by zero

Summary

Year-over-year growth compares current values to the same period last year.

Create a new measure using DAX with SAMEPERIODLASTYEAR and DIVIDE functions.

Ensure you have a proper date table for accurate time intelligence calculations.

Practice

1. What does year-over-year (YoY) growth measure in business intelligence?

easy

A. The total sales in the current year

B. The change in a value compared to the same period last year

C. The average value of all years combined

D. The difference between two unrelated time periods

Solution

1. Step 1: Understand the concept of YoY growth

   Year-over-year growth compares a metric from this year to the same metric from the previous year.

2. Step 2: Identify what YoY growth measures

   It measures the change or growth rate between these two periods to show progress or decline.

3. Final Answer:

   The change in a value compared to the same period last year -> Option B

4. Quick Check:

   YoY growth = change from last year [OK]

Hint: YoY compares this year to last year same period [OK]

Common Mistakes:

• Confusing YoY with total current year value
• Comparing unrelated time periods
• Using average of multiple years instead of last year

2. Which of the following DAX formulas correctly calculates year-over-year growth percentage for a measure named Sales?

easy

A. YoY Growth = DIVIDE([Sales] - CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date])), CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date])))

B. YoY Growth = [Sales] + CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date]))

C. YoY Growth = [Sales] / CALCULATE([Sales], NEXTYEAR('Date'[Date]))

D. YoY Growth = CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date])) - [Sales]

Solution

1. Step 1: Understand the YoY growth formula structure

   YoY growth percentage is (Current Year Sales - Last Year Sales) / Last Year Sales.

2. Step 2: Check each option's correctness

   YoY Growth = DIVIDE([Sales] - CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date])), CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date]))) uses DIVIDE to safely calculate the percentage difference using SAMEPERIODLASTYEAR, which is correct syntax and logic.

3. Final Answer:

   YoY Growth = DIVIDE([Sales] - CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date])), CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date]))) -> Option A

4. Quick Check:

   DAX YoY formula uses SAMEPERIODLASTYEAR and DIVIDE [OK]

Hint: Use DIVIDE with SAMEPERIODLASTYEAR for YoY % [OK]

Common Mistakes:

• Adding instead of subtracting sales values
• Using NEXTYEAR instead of SAMEPERIODLASTYEAR
• Not dividing by last year's sales

3. Given the following DAX measure:

YoY Growth = DIVIDE([Total Sales] - CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Calendar'[Date])), CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Calendar'[Date])))

If [Total Sales] for 2023 Q1 is 120,000 and for 2022 Q1 is 100,000, what is the YoY Growth value?

medium

A. 0.20

B. 1.20

C. -0.20

D. 220,000

Solution

1. Step 1: Calculate the difference between current and last year sales

   120,000 - 100,000 = 20,000

2. Step 2: Divide the difference by last year's sales

   20,000 / 100,000 = 0.20 (which is 20%)

3. Final Answer:

   0.20 -> Option A

4. Quick Check:

   (120,000 - 100,000) / 100,000 = 0.20 [OK]

Hint: Subtract last year, divide by last year sales [OK]

Common Mistakes:

• Confusing ratio with absolute difference
• Using current year as denominator
• Misreading the formula causing sign errors

4. You wrote this DAX measure to calculate YoY growth:

YoY Growth = DIVIDE([Sales] - CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date])), [Sales])

But the results seem incorrect. What is the main issue?

medium

A. DIVIDE function is missing a third argument for error handling

B. SAMEPERIODLASTYEAR cannot be used with CALCULATE

C. The denominator should be last year's sales, not current year's sales

D. The measure should use NEXTYEAR instead of SAMEPERIODLASTYEAR

Solution

1. Step 1: Analyze the formula denominator

   The denominator is currently [Sales] (current year), but YoY growth divides by last year's sales.

2. Step 2: Identify correct denominator usage

   It should be CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date])) to compare properly.

3. Final Answer:

   The denominator should be last year's sales, not current year's sales -> Option C

4. Quick Check:

   Divide by last year's sales for correct YoY [OK]

Hint: Divide difference by last year's sales, not current [OK]

Common Mistakes:

• Using current year sales as denominator
• Misunderstanding SAMEPERIODLASTYEAR usage
• Ignoring DIVIDE's optional error argument

5. You want to create a Power BI dashboard showing year-over-year growth for monthly sales. Your date table has missing months for some years. Which approach best ensures accurate YoY growth calculation?

hard

A. Replace SAMEPERIODLASTYEAR with PREVIOUSMONTH to avoid missing data issues

B. Calculate YoY growth only on months present in both years without filling missing months

C. Calculate YoY growth by comparing total sales of entire years ignoring months

D. Use a continuous date table with all months and apply SAMEPERIODLASTYEAR on it

Solution

1. Step 1: Understand the impact of missing months in date table

   Missing months cause SAMEPERIODLASTYEAR to fail or return incorrect results because it expects continuous dates.

2. Step 2: Use a continuous date table

   Creating a date table with all months ensures SAMEPERIODLASTYEAR works correctly and compares matching periods.

3. Step 3: Avoid partial or incorrect comparisons

   The other options either ignore missing data or use wrong functions, leading to inaccurate YoY growth.

4. Final Answer:

   Use a continuous date table with all months and apply SAMEPERIODLASTYEAR on it -> Option D

5. Quick Check:

   Continuous date table needed for accurate YoY [OK]

Hint: Ensure full date continuity for SAMEPERIODLASTYEAR [OK]

Common Mistakes:

• Ignoring missing months causing wrong YoY
• Using PREVIOUSMONTH instead of SAMEPERIODLASTYEAR
• Comparing totals ignoring monthly detail