Building a rolling 31-day sales total with DATESBETWEEN in DAX

Create model calculations by using DAX
Answer Correct answer: B — DATESBETWEEN bounds the range from MAX(date)-30 to MAX(date), an inclusive 31-day window.

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?

  1. CALCULATE(SUM(Sales[SalesAmount]), DATEADD(Date[Date], -30, DAY))
  2. CALCULATE(SUM(Sales[SalesAmount]), DATESBETWEEN(Date[Date], Max('Date'[Date])-30, Max('Date'[Date]))) Correct Answer
  3. CALCULATE(SUM(Sales[SalesAmount]), DATESMTD(Date[Date]))
  4. CALCULATE(SUM(Sales[SalesAmount]), DISTINCTCOUNT(Date[Date]) = 31)
rolling 31 day total datesbetween dax time intelligence

Community Votes

B
68%
A
32%

68% of anonymous learners picked answer B. Votes are pick records left by other test-takers — they are not the verified answer.

Community Insight

DATESBETWEEN(Date[Date], MAX(Date[Date])-30, MAX(Date[Date])) constructs the exact 31-day window ending at the selected date — the inclusive bounds on both ends are what make it 31 days rather than 30.

A Sales table relates to a Date table many-to-one, and a measure must return the total sales amount for a selected date plus the previous 30 days, so the filter expression must span exactly 31 consecutive dates.

Substituting DATEADD for a rolling window — DATEADD(Date[Date], -30, DAY) shifts the date set 30 days back (a single matching day, not a span), which is a sum over one day, not 31.

Community Discussion (12 comments)

MonikaStop 👍 11 Selected: B
DATESBETWEEN creates a continuous range of dates from the selected date back 30 days. So option B is more suitable for calculating a rolling 31-day total because it correctly defines the date range needed for the calculation.
Jayaruwan 👍 7 Selected: A
Answer A is correct because Dateadd provides required set of dates
SylUK 👍 1 Selected: B
B is the correct answer because the question says: 'Create a DAX measure for a rolling 31 days sales total that will return the total sales amount for a SELECTED DATE ( that can be yesterday, today,27/01/2025 or tomorrow. Etc for example) and the PREVIOUS 30 DAYS' In my point of view, this question is asking us to aggregate the sales amount from the PREVIOUS 30 DAYS to the SELECTED DATE. Therefore DATEBETWEEN is the perfect function to use in this case. It can't be DATEADD function because it is a comparison function and the question is not looking for a comparison between the 2 periods ( SELECTED DATE and the PREVIOUS 30 DAYS) . The question is looking for the total sales amount for a specified period (SELECTED DATE and the PREVIOUS 30 DAYS) instead.
Pey1nkh 👍 2 Selected: B
The DATESBETWEEN function defines a range of dates: From MAX(Date[Date]) - 30 (30 days before the selected date) To MAX(Date[Date]) (the selected date). This correctly captures all dates in the 31-day rolling window. why not Dateadd: The DATEADD function shifts the entire date context backward by 30 days. This means the calculation will consider only the values on dates exactly 30 days ago and not the entire rolling range of 31 days.
jaume 👍 2 Selected: B
DATEADD would return the selected date shifted back 30 days not a period. DATESBETWEEN will return the desired period for the calculation
3433bf6 👍 2 Selected: B
B DATESBETWEEN as DATEADD returns only 1date while we need a series of dates.
MonikaStop 👍 1 Selected: A
I changed my mind, Max(Date[Date]-30) will not work, so I would go with DATEADD
shedi13 👍 3
I would say A, but after testing im certain thats B
b92fc92 👍 1 Selected: B
B in my opinion
Monsta 👍 2 Selected: B
B is the right answer.
b9e3c84 👍 2 Selected: A
The expresion B Max(Date[]-30) is wrong
nelrosell 👍 1
A is the correct answer

Comments & Corrections

No comments yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Submitted for moderation before publishing. Keep it helpful and respectful.

Expert Analysis

Why the Answer Is Correct

DATESBETWEEN returns a contiguous date range with inclusive endpoints. MAX('Date'[Date]) in the measure's filter context is the selected date, so bounding the range from that date minus 30 days up to that date yields exactly 31 calendar days. CALCULATE then sums SalesAmount over that window, giving the rolling 31-day total the question defines as the selected date plus the previous 30 days.

Why the Other Options Are Wrong

DATEADD(Date[Date], -30, DAY) (A) time-shifts the current date selection 30 days into the past, producing the single day 30 days earlier — a one-day sum, not a 31-day window. DATESMTD (C) accumulates month-to-date, which resets on the first of each month and has nothing to do with a rolling window. The DISTINCTCOUNT comparison (D) is not even a valid filter-table expression for CALCULATE; it evaluates to a boolean rather than a table of dates.

Community Comment Notes

The most-endorsed comment explains that DATESBETWEEN creates the continuous range from the selected date back 30 days, which is exactly the 31-day requirement. Another commenter points out DATEADD merely shifts the selection, echoing the standard trap this question sets.

Official Reference

https://learn.microsoft.com/en-us/dax/datesbetween-function-dax

Related Analysis

You have a Power BI report that contains the table shown in

A-Grade

You have a Power BI report that contains a card. The card di

A-Grade

You use Power Query Editor to preview a column named Date as

S-Grade

Practice All PL-300 Questions

Access 116 questions with complete answers and detailed explanations.

View Full PL-300 Practice Test →

← Back to PL-300 Study Guide