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

PL-300 By islever Editorial Last updated

Create model calculations by using DAX

Short answer

B DATESBETWEEN bounds the range from MAX(date)-30 to MAX(date), an inclusive 31-day window.

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?
  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)

Key Takeaways

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.

Common mistake: 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.

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

This link opens the official vendor documentation in a new tab.

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.

Discussion 12 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 12

  1. MonikaStop 👍 11 Answered 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.
  2. Jayaruwan 👍 7 Answered A
    Answer A is correct because Dateadd provides required set of dates
  3. shedi13 👍 3
    I would say A, but after testing im certain thats B
  4. Pey1nkh 👍 2 Answered 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.
  5. jaume 👍 2 Answered B
    DATEADD would return the selected date shifted back 30 days not a period. DATESBETWEEN will return the desired period for the calculation
Show 7 more comments
  1. 3433bf6 👍 2 Answered B
    B DATESBETWEEN as DATEADD returns only 1date while we need a series of dates.
  2. b9e3c84 👍 2 Answered A
    The expresion B Max(Date[]-30) is wrong
  3. SylUK 👍 1 Answered 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.
  4. MonikaStop 👍 1 Answered A
    I changed my mind, Max(Date[Date]-30) will not work, so I would go with DATEADD
  5. Monsta 👍 2 Answered B
    B is the right answer.
  6. nelrosell 👍 1
    A is the correct answer
  7. b92fc92 👍 1 Answered B
    B in my opinion

Reader comments & corrections

No reader corrections 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.

PREVIOUSYEAR returns the full previous year, not the same day of last year

S-grade

Shrinking a Power BI model for monthly transaction totals with a star schema

S-grade

Northwind Traders semantic model: choosing Import storage mode for the Power BI tables

S-grade

Connecting Power BI Desktop to Azure DevOps Analytics views with the OData Feed connector

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