Year-over-year growth in Power BI - Dashboard Guide

Dashboard Mode - Year-over-year growth

Business Question

How much did sales grow compared to the same period last year?

Sample Data

Year	Month	Sales
2022	January	1000
2022	February	1200
2022	March	1100
2023	January	1300
2023	February	1500
2023	March	1400

Dashboard Components

• KPI Card: Total Sales Current Year
  Formula: Total Sales = SUM(Sales[Sales])
  For 2023, total sales = 1300 + 1500 + 1400 = 4200
• KPI Card: Total Sales Previous Year
  Formula: Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Sales[Date]))
  For 2022, total sales = 1000 + 1200 + 1100 = 3300
• KPI Card: Year-over-Year Growth %
  Formula: YoY Growth % = DIVIDE([Total Sales] - [Sales LY], [Sales LY], 0)
  Result: (4200 - 3300) / 3300 = 0.2727 or 27.27%
• Line Chart: Monthly Sales Comparison
  X-axis: Month
  Y-axis: Sales
  Legend: Year (2022 and 2023)
  Shows monthly sales trend side by side for both years

Dashboard Layout

+----------------------+----------------------+
| Total Sales 2023     | Total Sales 2022     |
|       4200           |       3300           |
+----------------------+----------------------+
| Year-over-Year Growth % (27.27%)           |
+--------------------------------------------+
|                                            |
+------------+------------------------------+
             | Monthly Sales Comparison Chart  |
             +------------------------------+

Interactivity

A slicer for Year allows selecting 2022 or 2023 or both. Selecting a year updates the KPI cards and the line chart to show data only for that year or years.

Hovering over the line chart shows exact sales per month per year.

Self Check

Add a filter to show only February data. Which components update?

• The KPI cards recalculate sales and YoY growth only for February.
• The line chart updates to show sales for February only for both years.

Key Result

Dashboard shows total sales for current and previous year, year-over-year growth percentage, and monthly sales comparison.

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