DATEADD for period shifts in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to compare monthly sales with the same month last year to understand growth trends.

📊 Data: You have a sales dataset with columns: Date, Product, Region, and Sales Amount. Dates cover multiple years.

🎯 Deliverable: Create a report showing monthly sales and sales from the same month last year side by side, using DATEADD to shift the period.

Sample Data

Date	Product	Region	Sales Amount
2023-01-15	Widget A	North	1000
2023-01-20	Widget B	North	1500
2023-02-10	Widget A	South	1200
2023-02-15	Widget B	South	1300
2023-03-05	Widget A	East	1100
2023-03-25	Widget B	East	1400
2022-01-10	Widget A	North	900
2022-01-22	Widget B	North	1300
2022-02-12	Widget A	South	1000
2022-02-18	Widget B	South	1100
2022-03-08	Widget A	East	1050
2022-03-28	Widget B	East	1350

1

Step 1: Create a new measure to calculate total sales for the current period.

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

Expected Result

Total Sales sums all sales amounts for the selected period.

2

Step 2: Create a measure to calculate sales for the same month last year using DATEADD to shift the date by -1 year.

Sales Last Year = CALCULATE([Total Sales], DATEADD('Sales'[Date], -1, YEAR))

Expected Result

Sales Last Year shows total sales for the same month in the previous year.

3

Step 3: Add a line chart visual with 'Date' (month-year) on the axis, and both 'Total Sales' and 'Sales Last Year' as values.

Configure the chart axis to use 'Date' grouped by Month-Year, add 'Total Sales' and 'Sales Last Year' measures as lines.

Expected Result

The chart displays two lines: current year monthly sales and last year monthly sales for easy comparison.

4

Step 4: Format the date axis to show month and year clearly, and add a legend to distinguish the two lines.

Set axis format to 'MMM yyyy', enable legend with labels 'Total Sales' and 'Sales Last Year'.

Expected Result

The chart is easy to read with clear time labels and legend.

Final Result

Monthly Sales Comparison

Date       | Total Sales | Sales Last Year
------------------------------------------
Jan 2023   | 2500        | 2200
Feb 2023   | 2500        | 2100
Mar 2023   | 2500        | 2400

(Line chart with two lines showing these values over months)

✓Sales in January 2023 increased compared to January 2022.

✓February 2023 sales also show growth over last year.

✓March 2023 sales are slightly higher than March 2022.

Bonus Challenge

Create a measure to calculate sales growth percentage compared to the same month last year.

Show Hint

Use the formula: Sales Growth % = DIVIDE([Total Sales] - [Sales Last Year], [Sales Last Year], 0) * 100