DATESINPERIOD
Returns a set of dates that runs for a number of intervals from a start date, going back when the number is negative.
DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>), where interval is DAY, MONTH, QUARTER or YEARReturns: 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
- dates: the date column of the Calendar table. Required.
- start_date: the date the window is measured from, usually the last date in the context, such as MAX('Calendar'[Date]). Required.
- number_of_intervals: how many intervals to run. Use a negative number to go back, such as -12. Required.
- interval: DAY, MONTH, QUARTER or YEAR. Required.
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
- The anchor is the last date in the context. In a month row, MAX returns the last day of that month. In a day row, it returns that day.
- A partial current month makes the window end on the last date you have. If the last month of data is incomplete, anchor the window on the last date with data.
- Do not use AVERAGEX over the window to get a monthly average. That averages daily values and gives a figure that is much smaller than the monthly total.
- The Calendar must have no gaps, because the window is built from consecutive dates.
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.