DATESBETWEEN Function

DATESBETWEEN Function (DAX)

 

Returns a table that contains a column of dates that begins with the start_date and continues until the end_date.

DATESBETWEEN(<dates>,<start_date>,<end_date>)  

Parameters

TermDefinition
datesA reference to a date/time column.
start_dateA date expression.
end_dateA date expression.

A table containing a single column of date values.

If start_date is a blank date value, then start_date will be the earliest value in the dates column.

If end_date is a blank date value, then end_date will be the latest value in the dates column.

The dates used as the start_date and end_date are inclusive: that is, if the sales occurred on September 1 and you use September 1 as the start date, sales on September 1 are counted.

System_CAPS_ICON_note.jpg Note


The DATESBETWEEN function is provided for working with custom date ranges. If you are working with common date intervals such as months, quarters, and years, we recommend that you use the appropriate function, such as DATESINPERIOD.

This DAX function is not supported for use in DirectQuery mode. For more information about limitations in DirectQuery models, see http://go.microsoft.com/fwlink/?LinkId=219172.

The following sample formula creates a measure that calculates the 'Summer 2007 sales' for the Internet sales.

To see how this works, create a PivotTable and add the field, CalendarYear, to the Row Labels area of the PivotTable. Then add a measure, named Summer 2007 Sales, using the formula as defined in the code section, to the Values area of the PivotTable.

=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), DATESBETWEEN(DateTime[DateKey],  
    DATE(2007,6,1),  
    DATE(2007,8,31)  
  ))  

Time Intelligence Functions (DAX)
Date and Time Functions (DAX)
DATESINPERIOD Function (DAX)

Community Additions

ADD
Show:
© 2016 Microsoft