Year-over-year growth in Power BI - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does Year-over-year (YoY) growth measure in business data?

Year-over-year growth measures how a value, like sales or revenue, changes compared to the same period in the previous year. It helps see if the business is growing or shrinking over time.

intermediate

Write a simple DAX formula to calculate Year-over-year growth percentage for Sales.

YoY Growth % = DIVIDE( SUM(Sales[Amount]) - CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Date[Date])), CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Date[Date])) ) * 100

beginner

Why is it important to use a proper date table when calculating Year-over-year growth in Power BI?

A proper date table ensures correct time intelligence calculations. It helps DAX functions like SAMEPERIODLASTYEAR work correctly by providing continuous dates and proper relationships.

beginner

What visual type is best to show Year-over-year growth trends over months?

A line chart is best because it clearly shows changes over time and trends month by month, making it easy to compare current year and previous year growth.

intermediate

Explain what happens if you calculate YoY growth without filtering the same period last year.

If you don't filter the same period last year, the comparison will be wrong. You might compare current sales to total sales from last year or a wrong date range, giving misleading growth results.

Which DAX function helps to get the same period last year for YoY calculations?

A SAMEPERIODLASTYEAR

B TOTALYTD

C DATESBETWEEN

D FILTER

SAMEPERIODLASTYEAR returns the dates from the same period in the previous year, perfect for YoY comparisons.

What does a positive Year-over-year growth percentage indicate?

A Decrease compared to last year

B Increase compared to last year

C No change compared to last year

D Data error

A positive YoY growth means the value increased compared to the same period last year.

Which visual is NOT ideal for showing Year-over-year growth trends?

A Combo chart

B Bar chart

C Line chart

D Pie chart

Pie charts show parts of a whole, not trends over time, so they are not suitable for YoY growth.

What is the first step before calculating YoY growth in Power BI?

A Create a date table

B Add a pie chart

C Import images

D Write SQL queries

A date table is essential for time intelligence functions to work correctly.

If Sales this year are 120 and last year were 100, what is the YoY growth percentage?

A 100%

B 83%

C 20%

D 120%

YoY growth % = ((120 - 100) / 100) * 100 = 20%

Describe how to calculate Year-over-year growth in Power BI using DAX.

Think about comparing current and previous year values using time intelligence.

Explain why Year-over-year growth is useful for business decision making.

Consider how comparing the same time periods year to year helps understand trends.

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