Rolling Totals with DATESINPERIOD

1. Introducing DATESINPERIOD

The core of any rolling calculation is the ability to define a moving window of time. DATESINPERIOD is a DAX time intelligence function designed for precisely this task. It returns a table containing a single column of dates for a specified period.

The syntax for DATESINPERIOD has four parameters:
DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>)

1. 'Date'[Date]: The date column from your calendar table.
2. start_date: The date from which the period begins.
3. number_of_intervals: The number of intervals to include. A negative number goes backward in time.
4. interval: The unit of the interval, such as DAY, MONTH, QUARTER, or YEAR.

To get a quick and clear overview of how this function works, please watch the following short video from SQLBI.

As you saw, DATESINPERIOD is straightforward on its own. The magic happens when we use it inside CALCULATE to create a dynamic period that "rolls" with the context of a visual.

2. The Key to a "Rolling" Calculation

How do we make the period change for each row in a report? For example, for the row "March 31, 2023," we need the last 90 days ending on that date. For "April 1, 2023," we need the 90 days ending on that date.

The key is the <start_date> parameter. When we build a measure, we can use MAX('Date'[Date]) for this argument. Because of the filter context created by a visual (like a table or chart), MAX('Date'[Date]) dynamically resolves to the last visible date for that specific point. For a row representing a single day, it's that day. For a row representing a month, it's the last day of that month.

This concept is the most critical part of building rolling calculations. The following video explains this "context-switching" behavior of MAX perfectly.

3. Building the Rolling Total Measure

Now, let's combine these concepts to build our measure. We will create a measure for a 90-day rolling sales total. This follows the "theory -> example -> application" sequence you prefer.

First, ensure you have your base [Total Sales] measure:

Total Sales = SUM(Sales[Revenue])

Next, we create the rolling total measure. We'll use variables (VAR/RETURN), which you're familiar with, to keep the code clean and readable.

Sales 90-Day Rolling =
-- This measure calculates the sum of sales over the last 90 days
-- from the last date in the current filter context.
VAR LastDateInContext =
    MAX ( 'Date'[Date] )
VAR PeriodOf90Days =
    DATESINPERIOD (
        'Date'[Date],
        LastDateInContext,
        -90, -- We go 90 days back in time
        DAY
    )
VAR Result =
    CALCULATE (
        [Total Sales],
        PeriodOf90Days -- Use the generated table of dates as the new filter
    )
RETURN
    Result

How it works:

1. LastDateInContext captures the last date from the current filter context (e.g., the specific day or month in your visual).
2. PeriodOf90Days uses DATESINPERIOD to generate a table of all dates in the 90-day period ending on LastDateInContext.
3. CALCULATE evaluates [Total Sales] but replaces the original date filter with our new 90-day period.

The following video segment walks through this exact process, building the measure and verifying the results in a table visual.

4. A Robust Pattern for Rolling Calculations

The pattern you just learned is the foundation for all rolling period calculations. It can be adapted to calculate rolling averages, counts, or any other aggregation. The experts at SQLBI provide a slightly more advanced version that is a great example of a production-ready measure.

Please read the following article from SQLBI. It recaps the DATESINPERIOD function and presents a complete DAX measure for a rolling 12-month average. While it calculates an average (AVERAGEX), the core logic using DATESINPERIOD is identical to our rolling total.

Notice how their pattern also includes logic to prevent the calculation from showing on dates before there is enough historical data. This is a great refinement for professional reports.

Conclusion

You have now added another powerful and widely-used technique to your DAX toolkit. By understanding how DATESINPERIOD generates a table of dates and how MAX('Date'[Date]) provides a dynamic anchor, you can confidently create rolling calculations for any time frame.

Key Takeaways:

• A rolling total smooths out data by summing values over a consistent, moving window of time (e.g., the last 90 days).
• The DATESINPERIOD function is the ideal tool for generating this moving window. It returns a table of dates based on a start date, a number of intervals, and an interval type.
• The key to making the period "roll" is using MAX('Date'[Date]) as the <start_date> argument within a measure. This makes the calculation dynamic to the filter context of your visual.
• The standard pattern is CALCULATE([Base Measure], DATESINPERIOD(...)), where DATESINPERIOD overrides the existing date filter.

Preview of the Next Lesson:
In our next lesson, we will continue with time intelligence, but shift from rolling periods to a different type of period-to-date calculation. You will learn to write a generic running total measure, which accumulates values from the beginning of a period (like a year) up to the current date, forming the basis for Year-to-Date (YTD) analysis.