DATEADD for period shifts in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Sales for Previous Month Using DATEADD

You have a Sales table with a Date column and a SalesAmount column. You want to create a measure that calculates total sales for the previous month using the DATEADD function.

Which of the following DAX measures correctly calculates the previous month's sales?

A Previous Month Sales = CALCULATE(SUM(Sales[SalesAmount]), PREVIOUSMONTH(Sales[Date]))

B Previous Month Sales = CALCULATE(SUM(Sales[SalesAmount]), DATEADD(Sales[Date], 1, MONTH))

C Previous Month Sales = CALCULATE(SUM(Sales[SalesAmount]), DATEADD(Sales[Date], -1, YEAR))

D Previous Month Sales = CALCULATE(SUM(Sales[SalesAmount]), DATEADD(Sales[Date], -1, MONTH))

💡 Hint

Remember, DATEADD shifts the dates by the number of intervals you specify. Negative values move backward in time.

Option D correctly uses DATEADD to shift the date context by -1 month, calculating sales for the previous month. Option D shifts forward by one month, option D shifts by year instead of month, and option D uses a different function (PREVIOUSMONTH) which is valid but not the DATEADD function asked for.

❓ visualization

intermediate

Visualizing Sales Shifted by Quarter Using DATEADD

You want to create a line chart showing total sales for the current quarter and the same quarter last year. You have a measure using DATEADD to shift the date by -4 quarters.

Which visual setup best helps compare these two periods clearly?

A Use a line chart with Date on the X-axis and two measures: Current Quarter Sales and Sales shifted by DATEADD with -4 quarters, with a legend for each measure.

B Use a stacked bar chart with Date on the X-axis and total sales only, no shifted measure.

C Use a pie chart showing sales for the current quarter only.

D Use a table visual listing sales by month without any time shift.

💡 Hint

To compare two time periods, a line chart with both measures helps see trends side by side.

Option A allows clear comparison of current and shifted sales over time. Other options do not show both periods or are not suitable for time series comparison.

❓ data_modeling

advanced

Handling Missing Dates in DATEADD Calculations

Your date table has missing dates for weekends. You use DATEADD to shift sales by one month. What problem can occur and how to fix it?

A DATEADD causes errors if dates are missing; fix by removing weekends from sales data.

B DATEADD will automatically fill missing dates; no fix needed.

C DATEADD may return blank results for missing dates; fix by ensuring the date table has continuous dates including weekends.

D DATEADD ignores missing dates and sums all sales regardless of date continuity.

💡 Hint

Think about how DATEADD works with continuous date ranges.

DATEADD requires a continuous date column to shift periods correctly. Missing dates cause blanks in shifted results. Adding all dates including weekends to the date table fixes this.

🎯 Scenario

advanced

Using DATEADD to Compare Sales Year Over Year with Filters

You want to create a measure that calculates sales for the same period last year but only for a specific product category. Which DAX measure correctly applies the filter and uses DATEADD?

A Sales LY Category = CALCULATE(SUM(Sales[SalesAmount]), DATEADD(Sales[Date], -1, YEAR), FILTER(Sales, Sales[Category] = "Electronics"))

B Sales LY Category = CALCULATE(SUM(Sales[SalesAmount]), DATEADD(Sales[Date], -1, YEAR), Sales[Category] = "Electronics")

C Sales LY Category = CALCULATE(SUM(Sales[SalesAmount]), DATEADD(Sales[Date], -1, YEAR)) + FILTER(Sales, Sales[Category] = "Electronics")

D Sales LY Category = CALCULATE(SUM(Sales[SalesAmount]), FILTER(ALL(Sales), Sales[Category] = "Electronics"), DATEADD(Sales[Date], -1, YEAR))

💡 Hint

Filters inside CALCULATE can be table filters or filter expressions. Use FILTER for row context filters.

Option A correctly applies DATEADD for time shift and FILTER to restrict category within CALCULATE. Option A uses a boolean filter which is invalid syntax. Option A applies FILTER on ALL(Sales) which removes context incorrectly. Option A adds a table filter outside CALCULATE which is invalid.

🔧 Formula Fix

expert

Diagnosing Unexpected Results with DATEADD and Fiscal Year

You have a fiscal calendar starting in April. You use DATEADD to shift sales by -1 year, but the results do not align with fiscal years. What is the likely cause?

A DATEADD cannot shift by years; use MONTH instead.

B DATEADD uses the calendar year from the Date column, ignoring fiscal year settings; fix by creating a fiscal date table and using it in DATEADD.

C DATEADD automatically adjusts for fiscal years; no fix needed.

D The sales data must be sorted by fiscal year for DATEADD to work correctly.

💡 Hint

Think about how DATEADD interprets the date column and fiscal calendars.

DATEADD shifts dates based on the actual dates in the date column, which follow calendar years by default. To align with fiscal years, you need a date table that reflects fiscal periods and use it in your model and calculations.

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