Edit

Share via

DATESBETWEEN

Applies to: Calculated column Calculated table Measure Visual calculation

Note

This function is discouraged for use in visual calculations as it likely returns meaningless results.

For date column input, returns a table that contains a column of dates that begins with a specified start date and continues until a specified end date.

For calendar input, returns a table that begins with a specified start date and continues until a specified end date. The table contains all primary tagged columns and all time related columns.

This function is suited to pass as a filter to the CALCULATE function. Use it to filter an expression by a custom date range.

Note

If you're working with standard date intervals such as days, months, quarters, or years, it's recommended you use the better suited DATESINPERIOD function.

Syntax

DATESBETWEEN(<dates> or <calendar>, <StartDate>, <EndDate>)

Parameters

Term Definition
dates or calendar A column that contains dates or a calendar reference
StartDate A date/day expression. If calendar syntax is used, please use the same data type as the primary column tagged to the Day category.
EndDate A date/day expression. If calendar syntax is used, please use the same data type as the primary column tagged to the Day category.

Return value

For date column input, a table containing a single column of date values.
For calendar input, a table that contains all primary tagged columns and all time related columns.

Remarks

  • In the most common use case, dates is a reference to the date column of a marked date table.

  • If StartDate is BLANK, then StartDate will be the earliest value in the dates column. For calendar, it will be the first value in column that is tagged as day.

  • If EndDate is BLANK, then EndDate will be the latest value in the dates column. For calendar, it will be the last value in column that is tagged as day.

  • Dates used as the StartDate and EndDate are inclusive. So, for example, if the StartDate value is July 1, 2019, then that date will be included in the returned table (providing the date exists in the dates column).

  • For date column input, the returned table can only contain dates stored in the Dates column. So, for example, if the Dates column starts from July 1, 2017, and the StartDate value is July 1, 2016, the returned table will start from July 1, 2017.

  • For calendar input, if the input date is not found in tagged day column, it will be treated as BLANK and thus the first/last value will be used.

  • For calendar input, use the same data type and format as the tagged day column for the start date and end date. For example, if the column uses the format YYYY-Sn-Qn-Mnn-Wnn-Dnn (e.g., "2014-S2-Q4-M11-W45-D03"), the start date and end date must follow the same format (e.g., "2015-S2-Q4-M11-W45-D03"). Otherwise, the behavior is undefined.

  • This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Example

The following Sales table measure definition uses the DATESBETWEEN function to produce a life-to-date (LTD) calculation. Life-to-date represents the accumulation of a measure over time since the very beginning of time.

Notice that the formula uses the MAX function. This function returns the latest date that's in the filter context. So, the DATESBETWEEN function returns a table of dates beginning from the earliest date until the latest date being reported.

Examples in this article can be used with the sample Adventure Works DW 2020 Power BI Desktop model. To get the model, see DAX sample model.

Customers LTD =
CALCULATE (
    DISTINCTCOUNT ( Sales[CustomerKey] ),
    DATESBETWEEN ( 'Date'[Date], BLANK (), MAX ( 'Date'[Date] ) )
)

Consider that the earliest date stored in the Date table is July 1, 2017. So, when a report filters the measure by the month of June 2020, the DATESBETWEEN function returns a date range from July 1, 2017 until June 30, 2020.

Example for calendar based time intelligence

The following Sales table measure definition uses the DATESBETWEEN function to produce a life-to-date (LTD) calculation. Life-to-date represents the accumulation of a measure over time since the very beginning of time.

Notice that the formula uses the MAX function. This function returns the max datekey that's in the filter context. So, the DATESBETWEEN function returns a table of dates beginning from the earliest date until the latest date being reported. DateKey is used as an example to show that the "Day" category can be tagged with a column that is not date-typed

Customers LTD =
CALCULATE (
    DISTINCTCOUNT ( Sales[CustomerKey] ),
    DATESBETWEEN ( FiscalCalendar, BLANK (), MAX ( 'Date'[DateKey] ) )
)

Related content