Downloads
DAXTime intelligence: shift and compareIntermediate

DATESINPERIOD

Returns a set of dates that runs for a number of intervals from a start date, going back when the number is negative.

Syntax
DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>), where interval is DAY, MONTH, QUARTER or YEAR

Returns: A table of dates.

What it does

DATESINPERIOD returns a window of dates that ends on a start date and runs back for a set length. The usual use is a rolling 12 month window: the twelve months that end on the last date in the report. Finance teams use it for trailing twelve month revenue, for the last twelve months of expenses, and for rolling averages such as the last three months of cash burn.

Syntax and arguments

Example

A monthly report shows trailing twelve month revenue, the revenue for the twelve months ending on each row's last date. The measure anchors the window on the last date in the context.

Revenue Rolling 12M =
CALCULATE(
    [Total Revenue],
    DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH)
)

In the October 2026 row, the window covers the twelve months that end on October 31, 2026. In the November 2025 row, it covers the twelve months that end on November 30, 2025.

Example: a second, different use

A rolling three month average of monthly expenses, for a trend line in a budget review. The total is divided by three, not averaged day by day, so the result is the average monthly amount.

Expenses Avg Last 3M =
VAR Window3M = DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -3, MONTH)
RETURN
DIVIDE(CALCULATE([Total Expenses], Window3M), 3)

Things to watch

In business terms

DATESINPERIOD supports trailing twelve month reporting for lenders, rolling expense trends, rolling cash burn, and the last twelve months of revenue used in covenant tests and in the investor update. It keeps the period moving with the data.

Related functions

All DAX functions · View as Markdown · DAX fundamentals