Power Pivot Principles: The A to Z of DAX Functions – DATESBETWEEN

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at DATESBETWEEN.

The DATESBETWEEN function

The DATESBETWEEN function is one of the time intelligence functions.  This function returns a table with a single column of the dates between two [2] given dates.  It has the following syntax.

There are three [3] arguments in this function:

• Dates: this argument is required, and is a column reference containing dates
• StartDate: this a date expression
• EndDate: this is also a date expression.

Because the DATESBETWEEN function is a time intelligence function, there are few key notes you should consider when using it (or any time intelligence function):

• all dates need to be present for the years required.  All the days in this span, from January 1 to December 31, must be included in the Date table.  The Date table must include all dates from commencement to the last day of a fiscal year if the report solely refers to fiscal years
• a column with a DateTime or Date data type and unique values is required.  Typically, this column is known as Date.  Although it is common practice, this is not necessary when defining associations with other tables.  However, the ‘mark as Date Table’ feature should relate to the Date column, which must have distinct values
• the Date table must be designated as a date table in the model in case the relationship between the Date table and any other table is not dependent upon the Date.

Consider this example: we have the following Date table that contains dates from 1 January 2008 to 31 December 2008:

We can write the following expression in DAX to return the dates contained between two [2] specified dates (25 August 2020 and 31 August 2020):

This will return the following:

It should be noted that:

• if StartDate is BLANK, then StartDate will be the earliest value present in the Dates column
• if EndDate is BLANK, then EndDate will be the latest value present in the Dates column
• both StartDate and EndDate are inclusive, meaning that both StartDate and EndDate will be included in the resulting table (providing that these dates exist in the Dates column)
• the returned table can only contain dates that are present in the Dates column
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.