Power Pivot Principles: Introducing the DATESBETWEEN Function

Welcome back to the Power Pivot Principles blog.  This week, we are going to look at the DATESBETWEEN function.

DATESBETWEEN is a simple time intelligence function that returns a table that contains a column of dates that begins with the start_date and continues until the end_date.  It is especially useful to define a period as a filter in the CALCULATE function.

The DATESBETWEEN function returns a table containing a single column of date values.  It uses the following syntax to operate:

• The <dates> is a reference to a date/time column
• The <start_date> is a date expression
• The <end_date> is a date expression.

Consider the data table shown below (not displayed in full):

This data table contains the sales amount for a specific date with different product types. We also create another calendar table in PowerPivot by using the method introduced here. The two tables have a relationship like:

If we want to calculate the sales for a specific product type in a defined period, we create the measure using the DATESBETWEEN function.

In this case, we define the start date with DATE function as 1 July 2016 and the end date as 31 December 2016 and allocate the date parameters to variable StartingDate and EndingDate respectively. We use CALCULATE function to calculate the total sales with filter on the result returned from DATESBETWEEN and use the variable SalesBetweenDate to assign the value of total sales. We could summarise this in a PivotTable as follows:

The table above shows the difference between the total sales for the year 2016 and the sales between defined start date and end date for the year 2016. DATESBETWEEN provides the flexibility of choosing specific period as the filter for calculation.

Another way of using DATESBETWEEN is similar to using the time / date function DATESYTD. For example, we could write the measure below to calculate the cumulative sales for each month:

In this case, we use DATEADD to obtain the date one year before and use LASTDATE to find the last date in Calendar[Date]. Hence, we may calculate the sum of the sales by incorporating the DATESBETWEEN filter. Then, we create PivotTable based on the date, total sales and cumulative sales. The result would be (not displayed in full):

The CumulativeSales shows the result of rolling total for each month.

That’s it for this week!

Stay tuned 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.