# Power Pivot Principles: Introducing the DATESBETWEEN Function

7 January 2020

*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:

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

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