Year-over-year growth in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to see how sales have grown or declined compared to the same month last year.

📊 Data: You have monthly sales data for two years, including Year, Month, and Sales Amount.

🎯 Deliverable: Create a report showing monthly sales and the year-over-year growth percentage for each month.

Sample Data

Year	Month	Sales Amount
2022	January	10000
2022	February	12000
2022	March	11000
2022	April	13000
2023	January	11500
2023	February	12500
2023	March	14000
2023	April	15000

1

Step 1: Load the sales data into Power BI and ensure columns Year, Month, and Sales Amount are correctly typed.

No formula needed; just import and check data types.

Expected Result

Data table with Year as number, Month as text, Sales Amount as number.

2

Step 2: Create a calculated measure to sum sales for each month and year.

Total Sales = SUM('Sales'[Sales Amount])

Expected Result

Measure 'Total Sales' sums sales amounts correctly.

3

Step 3: Create a calculated measure to find sales for the same month last year.

Sales Last Year = VAR CurrentYear = MAX('Sales'[Year]) VAR CurrentMonth = MAX('Sales'[Month]) RETURN CALCULATE([Total Sales], FILTER(ALL('Sales'), 'Sales'[Year] = CurrentYear - 1 && 'Sales'[Month] = CurrentMonth))

Expected Result

Measure 'Sales Last Year' returns sales from the same month in the previous year.

4

Step 4: Create a calculated measure to calculate year-over-year growth percentage.

YoY Growth % = DIVIDE([Total Sales] - [Sales Last Year], [Sales Last Year], 0) * 100

Expected Result

Measure 'YoY Growth %' shows percentage change compared to last year.

5

Step 5: Build a table visual with Month and Year as rows, and add 'Total Sales' and 'YoY Growth %' as values.

Configure table visual: Rows = Month, Year; Values = Total Sales, YoY Growth %

Expected Result

Table shows monthly sales and year-over-year growth percentage for each month.

Final Result

Month     | Year | Total Sales | YoY Growth %
--------------------------------------------
January   | 2022 | 10000       | -
January   | 2023 | 11500       | 15.0%
February  | 2022 | 12000       | -
February  | 2023 | 12500       | 4.17%
March     | 2022 | 11000       | -
March     | 2023 | 14000       | 27.27%
April     | 2022 | 13000       | -
April     | 2023 | 15000       | 15.38%

✓Sales increased in January 2023 by 15% compared to January 2022.

✓March 2023 saw the highest growth of 27.27% compared to March 2022.

✓February 2023 had a modest growth of 4.17%.

✓April 2023 sales grew by 15.38% compared to April 2022.

Bonus Challenge

Create a line chart showing Total Sales and YoY Growth % over the months for both years.

Show Hint

Use Month on the X-axis, Total Sales and YoY Growth % as two lines, and use Year as legend or filter.