DATESBETWEEN
Returns the dates from the start date to the end date, including both ends.
DATESBETWEEN(<dates>, <start_date>, <end_date>)Returns: A table of dates.
What it does
DATESBETWEEN returns a fixed range of dates. CALCULATE uses it as a filter, so a measure covers exactly that range, whatever the report's date slicer shows. Finance teams use it for an audit period, a covenant test period, a fiscal quarter with fixed boundaries, or a custom date range chosen by a user from a parameter table.
Syntax and arguments
- dates: the date column of the Calendar table. Required.
- start_date: the first date of the range, included in the result. Required.
- end_date: the last date of the range, included in the result. Required.
Example
An auditor asks for revenue for the nine months from January 1 to September 30, 2026. The measure fixes the period, whatever the report slicer shows.
Revenue Audit Period =
CALCULATE(
[Total Revenue],
DATESBETWEEN('Calendar'[Date], DATE(2026, 1, 1), DATE(2026, 9, 30))
)
Both ends count, so September 30 is included. The result is the same no matter which month is in the report's row.
Example: a second, different use
A range chosen by the user in a parameter table with a start and an end. The measure reads the two dates and applies them to the Calendar.
Revenue in Selected Range =
VAR StartDate = MIN('Report_Range'[StartDate])
VAR EndDate = MAX('Report_Range'[EndDate])
RETURN
CALCULATE(
[Total Revenue],
DATESBETWEEN('Calendar'[Date], StartDate, EndDate)
)
Things to watch
- Both ends are included. For a range that stops before a date, use an end date one day earlier, or filter with a less-than condition.
- The start and end must be real dates, not text.
- The result is fixed by the two dates, so the report slicer on the Calendar may be overridden. Say so in the label.
- The Calendar must cover the whole range with no gaps.
In business terms
DATESBETWEEN is used for audit periods, covenant test periods, custom quarters, trailing windows with fixed boundaries, and any period a lender or an auditor defines in writing. Fixed boundaries make the number reproducible when the report is run again.