PL-300 · Question #406
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…
The correct answer is B. CALCULATE(SUM(Sales[SalesAmount]), DATESBETWEEN(Date[Date], Max('Date'[Date])-30. To calculate a rolling 31-day sales total in Power BI, including the selected date and the previous 30 days, the DATESBETWEEN DAX function is the appropriate choice.
Question
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?
Options
- ACALCULATE(SUM(Sales[SalesAmount]), DATEADD(Date[Date], -30, DAY))
- BCALCULATE(SUM(Sales[SalesAmount]), DATESBETWEEN(Date[Date], Max('Date'[Date])-30,
- CCALCULATE(SUM(Sales[SalesAmount]), DATESMTD(Date[Date]))
- DCALCULATE(SUM(Sales[SalesAmount]), DISTINCTCOUNT(Date[Date]) = 31)
How the community answered
(31 responses)- A6% (2)
- B77% (24)
- C3% (1)
- D13% (4)
Why each option
To calculate a rolling 31-day sales total in Power BI, including the selected date and the previous 30 days, the DATESBETWEEN DAX function is the appropriate choice.
DATEADD shifts an entire set of dates by a specified interval, which would not correctly define a rolling window ending on the selected date.
The DATESBETWEEN DAX function correctly defines a custom date range, making it suitable for rolling calculations. By setting the start date to MAX('Date'[Date]) - 30 and the end date to MAX('Date'[Date]), it accurately captures the sales for the current date and the preceding 30 days within the CALCULATE function's filter context.
DATESMTD calculates 'Dates Month To Date,' which sums values from the beginning of the current month to the current date, not a rolling 31-day period.
DISTINCTCOUNT(Date[Date]) = 31 is a logical condition that checks if 31 distinct dates are present, not a function that defines a specific rolling date range for calculation.
Concept tested: DAX time intelligence (rolling sums)
Source: https://learn.microsoft.com/en-us/dax/datesbetween-function-dax
Community Discussion
No community discussion yet for this question.