Edit

Share via

DATESBETWEEN

  • Article

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.

Returns a table that contains a column of dates that begins with a specified start date and continues until a specified end date.

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>, <StartDate>, <EndDate>)

Parameters

Term Definition
Dates A date column.
StartDate A date expression.
EndDate A date expression.

Return value

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.

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.

Related content