DATESBETWEEN

DATESBETWEEN is a DAX function that returns the dates between two given dates.

Syntax

DATESBETWEEN(
   Dates,
   StartDate,
   EndDate
)

Argument	Properties	Description
Dates		The name of a column containing dates or a one column table containing dates.
StartDate		Start date.
EndDate		End date.

Return Values

A table containing a single column of date values.

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.

• If EndDate is BLANK, then EndDate will be the latest value in the dates column.

• 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).

• 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.

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

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.

Related functions

Other functions related to DATESBETWEEN are:

• DATESINPERIOD

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/dax/datesbetween-function-dax