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

Practice

1. What does the DATEADD function do in Power BI?

easy

A. It shifts dates by a specified number of days, months, quarters, or years.

B. It adds two date columns together.

C. It formats dates into text strings.

D. It calculates the difference between two dates.

Solution

1. Step 1: Understand the purpose of DATEADD

   DATEADD is used to move dates forward or backward by a set period like days or months.

2. Step 2: Compare with other date functions

   Unlike difference or formatting functions, DATEADD shifts dates by a period.

3. Final Answer:

   It shifts dates by a specified number of days, months, quarters, or years. -> Option A

4. Quick Check:

   DATEADD shifts dates = D [OK]

Hint: Remember: DATEADD moves dates by periods, not calculates differences [OK]

Common Mistakes:

• Confusing DATEADD with DATEDIFF
• Thinking DATEADD formats dates
• Assuming DATEADD adds two dates

2. Which of the following is the correct syntax to shift dates by 1 month using DATEADD in DAX?

easy

A. DATEADD(DateColumn, '1 month')

B. DATEADD(DateColumn, 1, MONTH)

C. DATEADD(DateColumn, MONTH, 1)

D. DATEADD(1, DateColumn, MONTH)

Solution

1. Step 1: Recall DATEADD syntax

   DATEADD requires three arguments: a date column, number of intervals, and interval type.

2. Step 2: Match correct argument order

   The correct order is DATEADD(DateColumn, number, interval), so DATEADD(DateColumn, 1, MONTH) matches.

3. Final Answer:

   DATEADD(DateColumn, 1, MONTH) -> Option B

4. Quick Check:

   Correct syntax = A [OK]

Hint: Syntax order: DateColumn, number, then interval [OK]

Common Mistakes:

• Swapping interval and number arguments
• Using quotes around interval
• Passing number before date column

3. Given the measure:

Sales Last Month = CALCULATE(SUM(Sales[Amount]), DATEADD(Calendar[Date], -1, MONTH))

What does this measure calculate?

medium

A. Total sales for all months except the current one.

B. Total sales for the next month relative to the current filter context.

C. Total sales for the current month only.

D. Total sales for the previous month relative to the current filter context.

Solution

1. Step 1: Analyze DATEADD usage

   DATEADD shifts the date filter by -1 month, meaning one month back.

2. Step 2: Understand CALCULATE with shifted filter

   CALCULATE applies the shifted date filter to sum sales for that previous month.

3. Final Answer:

   Total sales for the previous month relative to the current filter context. -> Option D

4. Quick Check:

   DATEADD with -1 month = previous month sales [OK]

Hint: Negative number in DATEADD means past period [OK]

Common Mistakes:

• Thinking negative shifts future dates
• Assuming it sums current month sales
• Confusing previous and next month

4. You wrote this measure:

Sales Next Quarter = CALCULATE(SUM(Sales[Amount]), DATEADD(Calendar[Date], 1, QUARTER))

But it returns an error. What is the likely cause?

medium

A. The interval 'QUARTER' is not a valid argument for DATEADD.

B. The number argument must be negative to shift forward.

C. The date column is not continuous or missing dates.

D. CALCULATE cannot be used with DATEADD.

Solution

1. Step 1: Check validity of interval argument

   QUARTER is a valid interval for DATEADD, so The interval 'QUARTER' is not a valid argument for DATEADD. is incorrect.

2. Step 2: Understand DATEADD requirements

   DATEADD requires a continuous date column without gaps; missing dates cause errors.

3. Step 3: Verify number argument

   Positive number shifts forward, so The number argument must be negative to shift forward. is wrong.

4. Step 4: Confirm CALCULATE usage

   CALCULATE works with DATEADD filters, so CALCULATE cannot be used with DATEADD. is wrong.

5. Final Answer:

   The date column is not continuous or missing dates. -> Option C

6. Quick Check:

   Continuous date column needed = B [OK]

Hint: Ensure date column is continuous for DATEADD [OK]

Common Mistakes:

• Using non-continuous date columns
• Misunderstanding interval validity
• Thinking negative number shifts forward

5. You want to create a measure that compares sales of the current month to the same month last year using DATEADD. Which DAX formula correctly achieves this?

hard

A. Sales Same Month Last Year = CALCULATE(SUM(Sales[Amount]), DATEADD(Calendar[Date], -1, YEAR))

B. Sales Same Month Last Year = CALCULATE(SUM(Sales[Amount]), DATEADD(Calendar[Date], -12, QUARTER))

C. Sales Same Month Last Year = CALCULATE(SUM(Sales[Amount]), DATEADD(Calendar[Date], -1, MONTH))

D. Sales Same Month Last Year = CALCULATE(SUM(Sales[Amount]), DATEADD(Calendar[Date], -1, QUARTER))

Solution

1. Step 1: Understand the goal

   We want to compare sales for the same month but one year earlier.

2. Step 2: Analyze each option's date shift

   Sales Same Month Last Year = CALCULATE(SUM(Sales[Amount]), DATEADD(Calendar[Date], -1, MONTH)) shifts by 1 month back, not a year.
   Sales Same Month Last Year = CALCULATE(SUM(Sales[Amount]), DATEADD(Calendar[Date], -12, QUARTER)) shifts by 12 quarters back, which is 3 years, not one year.
   Sales Same Month Last Year = CALCULATE(SUM(Sales[Amount]), DATEADD(Calendar[Date], -1, YEAR)) shifts by 1 year back, directly matching the goal.
   Sales Same Month Last Year = CALCULATE(SUM(Sales[Amount]), DATEADD(Calendar[Date], -1, QUARTER)) shifts by 1 quarter back, which is 3 months.

3. Step 3: Choose the clearest and correct interval

   Using YEAR interval with -1 is the best practice for same month last year comparison.

4. Final Answer:

   Sales Same Month Last Year = CALCULATE(SUM(Sales[Amount]), DATEADD(Calendar[Date], -1, YEAR)) -> Option A

5. Quick Check:

   Shift by -1 YEAR for same month last year = C [OK]

Hint: Use YEAR interval with -1 to shift one year back [OK]

Common Mistakes:

• Using -12 QUARTER instead of -1 YEAR
• Shifting by -1 MONTH or -1 QUARTER incorrectly
• Confusing months and years intervals