DATEADD for period shifts in Power BI - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the DATEADD function do in Power BI?

DATEADD shifts a date by a specified number of intervals (days, months, quarters, years) to compare data across different time periods.

beginner

Write the syntax of DATEADD in Power BI.

DATEADD(<dates>, <number_of_intervals>, <interval>) where <interval> can be DAY, MONTH, QUARTER, or YEAR.

intermediate

How would you use DATEADD to get sales from the previous month?

Use DATEADD with -1 for the number_of_intervals and MONTH for the interval, like: DATEADD(Date[Date], -1, MONTH).

intermediate

Why is DATEADD useful for creating time comparison measures?

It helps compare current data with past periods, like last month or last year, enabling trend analysis and performance tracking.

beginner

What happens if you use DATEADD with a positive number_of_intervals?

It shifts the dates forward by that number of intervals, for example, moving one month ahead if interval is MONTH and number is 1.

What interval options can you use with DATEADD in Power BI?

A DAY, MONTH, QUARTER, YEAR

B HOUR, MINUTE, SECOND

C WEEK, FORTNIGHT, MONTH

D DAY, WEEK, YEAR

DATEADD supports DAY, MONTH, QUARTER, and YEAR intervals for shifting dates.

How do you shift dates one quarter back using DATEADD?

A DATEADD(Date[Date], -1, QUARTER)

B DATEADD(Date[Date], 1, QUARTER)

C DATEADD(Date[Date], -1, MONTH)

D DATEADD(Date[Date], 1, MONTH)

Use -1 for number_of_intervals and QUARTER for interval to shift one quarter back.

What is the result of DATEADD(Date[Date], 0, MONTH)?

A Shifts dates to the next month

B Returns the original dates without change

C Shifts dates to the previous month

D Returns an error

Using 0 as number_of_intervals returns the original dates unchanged.

Which scenario is best suited for using DATEADD?

A Calculating total sales for a single day

B Sorting data alphabetically

C Comparing sales this month to sales last month

D Filtering data by product category

DATEADD is used to compare data across different time periods, like this month vs last month.

If you want to compare this year's sales to last year's, which DATEADD call is correct?

A DATEADD(Date[Date], 1, YEAR)

B DATEADD(Date[Date], 12, MONTH)

C DATEADD(Date[Date], -12, MONTH)

D DATEADD(Date[Date], -1, YEAR)

Use -1 for number_of_intervals and YEAR for interval to shift dates one year back.

Explain how DATEADD helps in creating time comparison reports in Power BI.

Think about comparing sales this month to last month.

Describe the syntax and parameters of the DATEADD function and what each part means.

Break down the function into its three parts.