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

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

The DATESINPERIOD function

The DATESINPERIOD function is one of the time intelligence functions.  It returns a table that contains a column of dates, beginning with a specified start date and continuing for a specified number of defined intervals.  This function can be used as a filter to the CALCULATE function, filtering an expression by standard date intervals such as days, months, quarters, or years.  It operates using the following syntax:

There are three [3] components in this function:

• dates: this argument is required and is a date column
• start_date: this is a date expression
• number_of_intervals: this argument is required and is an integer that specifies the number of intervals to add or subtract
• interval: this argument is required and is the interval by which to shift the dates.  The interval can be DAY, MONTH, QUARTER, or YEAR.

Because the DATESINPERIOD 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 the following example: we have a table named Date_Table that contains dates ranging from 1 January 2020 to 31 December 2020:

Now, we want the query to return one [1] day starting from 15 August 2020, so we write the following DAX query:

This will return the following:

If we want the query to return three [3] days starting from 15/08/2020 we can write the following DAX query:

This will return the following:

If we set the interval to zero [0] an empty table will be returned.

If we set the interval to a negative number, it will return a number of days before 15 August 2020 inclusively.  As an example, we will set our interval to negative three [-3]:

Which will result in:

The date is counted backwards starting from 15 August 2020 to 13 August 2020 and is displayed in ascending order.

It should be further noted that:

• the dates argument must be a reference to a date/time column
• the resulting table can only include dates that exist in the date column
• a BLANK value in the start_date argument will equate to the earliest date within the dates table.

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.