Microsoft Power BI Data Analyst — Question 126

You have a Power BI model that contains two tables named Sales and Date. The Sales table relates to the Date table by using a many-to-one relationship. The Sales table contains the following columns:

• Date
• Product
• SalesAmount

You need to create a DAX measure for a rolling 31-day sales total that will return the total sales amount for a selected date and the previous 30 days.

Which DAX expression should you use?

Answer options

• A. CALCULATE(SUM(Sales[SalesAmount]), DATEADD(Date[Date], -30, DAY))
• B. CALCULATE(SUM(Sales[SalesAmount]), DATESBETWEEN(Date[Date], Max('Date'[Date])-30, Max('Date'[Date])))
• C. CALCULATE(SUM(Sales[SalesAmount]), DATESMTD(Date[Date]))
• D. CALCULATE(SUM(Sales[SalesAmount]), DISTINCTCOUNT(Date[Date]) = 31)

Correct answer: B

Explanation

The correct answer is B because it uses the DATESBETWEEN function to define a range from the maximum date to 30 days prior, thereby accurately calculating the rolling total for the last 31 days. Option A incorrectly uses DATEADD, which does not effectively create a range, while option C calculates month-to-date totals rather than a 31-day rolling total. Option D is not applicable as it counts distinct dates instead of summing sales amounts over the specified period.