There are many time intelligence functions in DAX, and each of those is helping in aspects of analyzing data on dates. There are two functions which work very similar to each other but have a bit different usage; DatesInPeriod, and DatesBetween. In this post, I will show you what is the difference between these two functions, and scenarios that you can use each. DatesBetween and DatesInPeriod both give you a period of dates, but let’s see their main difference. If you like to learn more about Power BI; read Power BI book from Rookie to Rock Star.

Sample Dataset

For examples of this post, you need the FactInternetSales table from AdventureWorksDW example.

DatesInPeriod

The DatesInPeriod function in DAX will give you all dates within a period. The period can be one of these: Day, Month, Quarter, Year. Here is the syntax of using this function;

DATESINPERIOD(<dates>,<start_date>,<number_of_intervals>,<interval>)

Here is a description of input parameters;

• <dates>: The date field (like many other time intelligence functions, this function also requires a date field)
• <start_date>: The start date that period starts from/to it (depends if the interval is a positive or negative number)
• <number_of_intervals>: a positive or negative number that starts from the start date based on the interval
• <interval>: Year, Quarter, Month, or Day intervals

The output of this function is a table of dates within the period specified. Let’s see how this function can be used. For example, If you want to get all dates in the last year’s period from the date of the filter context, it can be a calculation like this;

DATESINPERIOD(FactInternetSales[OrderDate].[Date],
    LASTDATE(FactInternetSales[OrderDate].[Date]),-1,YEAR)

Note that FactInternetSales[OrderDate] is just a normal date field in the FactInternetSales table and the reason that I used “.[Date]” at the end of it, is because I am using the built-in date dimension of Power BI. If you use your own date dimension and have set it as a date table, then you should exclude the “.[Date]” part of this expression. To get the current filter context’s date as the start date, I used the LASTDATE() DAX function, and we are going a Year back in the interval. so the number of intervals is –1. The expression above returns a table, and cannot be used as a measure. Just to show you how this can work, I put it inside another function as a measure.

Example: Sales for the Last Rolling Year from the current Date

An example of using DatesInPeriod is to calculate the sales of the last year from the current date. In the expressions above, you’ve seen how we can get all dates in the period of the last year from the current date in the filter context. We just need to put it inside a Calculate statement to get Sum of Sales for that period.

Sales for the Last Rolling Year = 
CALCULATE(
    SUM(FactInternetSales[SalesAmount]),
DATESINPERIOD(
    FactInternetSales[OrderDate].[Date],
    LASTDATE(FactInternetSales[OrderDate].[Date]),
    -1,
    YEAR)
)

What is the period of the calculation?

The important question in the above calculation is that what is the period of the calculation? Is this from the first of the year? or is it starting from a different date? what is included and what is excluded? The answer is that; DatesInPeroid starts from the <start_date> (which in this case is the month in every row of the table visualized in the screenshot above), and it will go one year back (because the interval is the year, and the number of intervals is -1). For example; If the current month April 2007, then it will go one year back from that date. But does it mean it will start from April 2006, or May 2006? Well, DatesInBetween is a smart function and will exclude the start date to avoid double counting. It will start in May 2006. Let’s see what is the period start and period end.

To get the period start and period end, you can create two measures below using FIRSTDATE() and LASTDATE() functions;

First Date in the Period for DatesInPeriod = 
FIRSTDATE(
DATESINPERIOD(FactInternetSales[OrderDate].[Date],
    LASTDATE(FactInternetSales[OrderDate].[Date]),-1,YEAR)
)

and for the last date;

Last Date in the Period for DatesInPeriod = 
LASTDATE(
DATESINPERIOD(FactInternetSales[OrderDate].[Date],
    LASTDATE(FactInternetSales[OrderDate].[Date]),-1,YEAR)
)

Now you can see the period clearly in Power BI;

As you can see in the yellow highlighted section; for April 2007, the Rolling Last Year Sales is $5,994,882.35, which is for the period between the 1st of May 2006 to 30th of April 2007. As you can see it starts not from the 30th of April 2006 to avoid double counting. DatesInPeriod makes your like much easier to calculate dates in a period. That is why it is called DatesInPeriod! So the value of Rolling Last Year Sales is the accumulation of all sales from May 2006 to April 2007.

DatesInPeriod is perfect DAX function for calculating standard periods which follow Day, Month, Quarter, and Year intervals. It will exclude unnecessary dates for you.

DatesBetween

DatesBetween function in DAX is a more generic version of DatesInPeriod. You have more flexibility with this function. With this function, you do not need to worry about the interval or number of intervals. This function will give you all the dates between a start date and an end date. Here is the syntax of this function;

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

Parameters are:

• <dates>: The date field (like many other time intelligence functions, this function also requires a date field)
• <start_date>: The start date that period starts from it (unlike DatesInPeriod, this cannot go backward from the start date. It always go forward from there)
• <end_date>: The end date that period ends there.

The output of this function is a table of dates from the start_date to the end_date including both start and end date.

An important understanding of this function is that the function itself doesn’t go back or forth from the start date to give you the period. You have to calculate the start or the end date first, and then get the period based on that. For example; Let’s say we want to calculate dates in the last rolling year from the current date in the filter context (similar to the example we have done with DatesInPeriod). You need to first find out what your start date is.

DATEADD(LASTDATE(FactInternetSales[OrderDate].[Date]),-1,YEAR)

The expression above is using DATEADD() function to calculate the start date which is going to be a year before (because the interval is -1) from the start date, which is calculated with LASTDATE().

After calculating the start date, you can use it inside a DatesBetween function like this;

DATESBETWEEN(FactInternetSales[OrderDate].[Date],
    DATEADD(LASTDATE(FactInternetSales[OrderDate].[Date]),-1,YEAR)
    ,LASTDATE(FactInternetSales[OrderDate].[Date])
)

The first parameter is just the date field. the second parameter is the start date that we have calculated, and the last parameter is the end date.

DatesBetween is a good function to use when the start and end of the period are determined. here is an example of calculating the sale of a specific period.

Sales of Specific Period with DatesBetween = 
CALCULATE(
    SUM(FactInternetSales[SalesAmount]),
DATESBETWEEN(
    FactInternetSales[OrderDate].[Date],
    DATE(2007,8,1),
    DATE(2007,11,16)
    )
)

DatesInPeriod vs DatesBetween

Now let’s see if we use the DatesBetween for calculating the period and get the start and end of that period what we get as a result;

First DatesBetween = 
FIRSTDATE(
DATESBETWEEN(
    FactInternetSales[OrderDate].[Date],
    DATEADD(LASTDATE(FactInternetSales[OrderDate].[Date]),-1,YEAR)
    ,LASTDATE(FactInternetSales[OrderDate].[Date])
    )
)

and the calculation for the end of the period;

Last DatesBetween = 
LASTDATE(
DATESBETWEEN(
    FactInternetSales[OrderDate].[Date],
    DATEADD(LASTDATE(FactInternetSales[OrderDate].[Date]),-1,YEAR)
    ,LASTDATE(FactInternetSales[OrderDate].[Date])
    )
)

Here is the result compared to DatesInPeriod;

As you can see in the above screenshot, the output of DatesBetween INCLUDES both start and end date, it will start from 30th of April 2006, while the DatesInPeriod starts from 1st of My 2006. so the first difference between these two functions is that one of the is inclusive of both dates (DatesBetween).

DatesBetween is a period of dates inclusive of both start and end date. DatesInPeriod is giving you the period of dates and excluding unwanted dates.

Another difference between these two is the input parameters that you have. Sometimes, you have the start and end date, and you want to get all dates in that period, DatesBetween is definitely a good function to use in this situation. Sometimes, you do not have both ends of the period, you just have one, and the interval, in that case, DatesInPeriod is your best friend. There are many scenarios that you can use DatesBetween and DatesInPeriod instead of the other one, here is an example that I wrote a previous dynamic period calculation with DatesBetween.

If you have the start and end date, and you want to get all dates in that period, DatesBetween is definitely a good function to use. However, Ssometimes, you do not have both ends of the period, you just have one, and the interval, in that case, DatesInPeriod is your best friend

Summary

DatesBetween and DatesInPeriod are DAX functions to give you a period of dates. DatesBetween gives you dates from a start date to an end date. DatesInPeriod will give you an interval of dates from a particular period. Each function has its own usages, you can tweak and change your expressions with each of these functions to get the same result as the other function (like anything else in DAX!). However, these two functions will give you good power in different situations of calculating a period.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.