When creating Time Intelligence Measures with Energy BI or in Cloth in Semantic Fashions, it may be essential to create a date vary to calculate the consequence for a selected timeframe.
To be exact, nearly all the Time Intelligence capabilities in Dax create a listing of dates for a date vary.
However generally we should create a customized date vary on account of particular necessities.
DAX provides us two capabilities for this job:
Each capabilities take a Begin Date as a parameter.
However for the Finish Date, the habits is totally different.
Whereas DATESINPERIOD() takes Intervals (Days, Months, Quarters, Years), DATESBETWEEN() takes a specified Date used because the Finish Date.
In distinction, DATEADD() makes use of the present Filter Context to get the Begin Date and to calculate the Finish Date.
However we wish to cross a Begin Date, which might differ from the Date(s) within the present Filter Context.
That is when one of many capabilities talked about above comes into play.
On the finish of this text, I’ll present you a sensible instance utilizing the strategies proven right here.
Instruments and state of affairs
Like in lots of different articles, I exploit DAX Studio to jot down DAX Queries and analyze the outcomes.
In case you are not accustomed to writing DAX queries, learn my piece on the best way to study to jot down such queries:
This time, I exploit the Knowledge mannequin just for the Date desk.
I wish to calculate a date vary ranging from Might 5. 2025 and both 25 days or 2 Months into the longer term.
To set the beginning date, I exploit this expression:
DEFINE
VAR StartDate = "2025-05-05"
EVALUATE
{ StartDate }That is the lead to DAX Studio:
I outline a Variable and assign the results of the date expression for the next queries.
One other technique to outline the beginning date is to create a date worth utilizing DATE(2025, 05, 05).
The consequence would be the identical.
The distinction between these two approaches is that the primary returns a string, however the second returns a correct date.
The DAX capabilities used right here can work with each.
Utilizing DATESINPERIOD()
Let’s begin with DATEINPERIOD().
I’ll use this operate to get a date vary string from the Begin Date and 25 days into the longer term:
DEFINE
VAR StartDate = "2025-05-05"
EVALUATE
DATESINPERIOD('Date'[Date]
,StartDate
,25
,DAY)The result’s a desk with 25 rows for the times ranging from Might 05, 2025, to Might 29, 2025:
Now, let’s barely change the question to get a listing of all dates from the Begin Date to 2 Months into the longer term:
DEFINE
VAR StartDate = "2025-05-05"
EVALUATE
DATESINPERIOD('Date'[Date]
,StartDate
,2
,MONTH)The question returns 61 rows ranging from Max 05, 2025, till July 04, 2025:
I can cross the interval with an arbitrary variety of days (e.g., 14, 28, 30, or 31 days), and the operate robotically calculates the date vary.
Once I cross unfavorable numbers, the date vary goes to the previous, beginning with the beginning date.
Utilizing DATESBETWEEN()
Now, let’s have a look at DATESBETWEEN().
DATESBETWEEN() takes a Begin- and an Finish-Date as parameters.
This implies I need to calculate the top date earlier than utilizing it.
Once I wish to get a date vary from Might 05 to Might 29, 2025, I need to use the next question:
DEFINE
VAR StartDate = "2025-05-05"
VAR EndDate = "2025-05-25"
EVALUATE
DATESBETWEEN('Date'[Date]
,StartDate
,EndDate)The consequence is similar as with DATESINPERIOD().
Nevertheless, there may be one essential level: The tip date is included within the consequence.
This implies I can write one thing like this to get a date vary over two months from Might 05 to July 05, 2025:
DEFINE
VAR StartDate = "2025-05-05"
VAR EndDate = "2025-07-05"
EVALUATE
DATESBETWEEN('Date'[Date]
,StartDate
,EndDate)The result’s similar to the one utilizing DATESINPERIOD() and month because the interval, however with one row extra:
This provides me extra flexibility to create the date ranges, as I can pre-calculate the top date in response to my wants.
Use in Measures – a sensible instance.
I can use these strategies to calculate a operating complete in a Measure.
However we should take care to make use of the 2 capabilities in the best means
For instance, to calculate the operating complete per thirty days for 25 days.
Have a look at the next code, the place I outline two Measures utilizing the 2 capabilities:
DEFINE
MEASURE 'All Measures'[25DayRollingTotal_A] =
VAR DateRange =
DATESINPERIOD('Date'[Date]
,MIN ( 'Date'[Date] )
,25
,DAY)
RETURN
CALCULATE ( [Sum Online Sales]
, DateRange )
MEASURE 'All Measures'[25DayRollingTotal_B] =
VAR DateRange =
DATESBETWEEN ( 'Date'[Date]
,MIN ( 'Date'[Date] )
,MIN ( 'Date'[Date] ) + 25 )
RETURN
CALCULATE ( [Sum Online Sales]
, DateRange )
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
'Date'[Year]
,'Date'[Month]
,"Gross sales", [Sum Online Sales]
,"25DayRollingTotal_A", [25DayRollingTotal_A]
,"25DayRollingTotal_B", [25DayRollingTotal_B]
)
,'Date'[Date] >= DATE(2023, 01, 01) && 'Date'[Date] <= DATE(2023, 12, 31)
)
ORDER BY 'Date'[Month]That is the consequence:
Discover the distinction between the 2 outcomes.
It is because DATESBETWEEN() contains the top date within the consequence, whereas DATESINPERIOD() provides the variety of intervals to the beginning date however contains the beginning date.
Strive it out with the next question:
DEFINE
VAR StartDate = DATE(2025,05,05)
VAR EndDate = StartDate + 25
EVALUATE
DATESINPERIOD('Date'[Date]
,StartDate
,25
,DAY)
EVALUATE
DATESBETWEEN('Date'[Date]
,StartDate
,EndDate)The primary returns 25 rows (Might 05 – Might 29, 2025) and the second returns 26 rows (Might 05 – Might 30, 2025).
Due to this fact, I need to change one of many two Measures to get the identical consequence.
On this case, the calculation definition is: Begin from the primary date and go 25 into the longer term.
The corrected logic is that this:
DEFINE
MEASURE 'All Measures'[25DayRollingTotal_A] =
VAR DateRange =
DATESINPERIOD('Date'[Date]
,MIN ( 'Date'[Date] )
,25
,DAY)
RETURN
CALCULATE ( [Sum Online Sales]
, DateRange )
MEASURE 'All Measures'[25DayRollingTotal_B] =
VAR DateRange =
DATESBETWEEN ( 'Date'[Date]
,MIN ( 'Date'[Date] )
,MIN ( 'Date'[Date] ) + 24 ) // 24 as a substitute of 25 days
RETURN
CALCULATE ( [Sum Online Sales]
, DateRange )
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
'Date'[Year]
,'Date'[Month]
,"Gross sales", [Sum Online Sales]
,"25DayRollingTotal_A", [25DayRollingTotal_A]
,"25DayRollingTotal_B", [25DayRollingTotal_B]
)
,'Date'[Date] >= DATE(2023, 01, 01) && 'Date'[Date] <= DATE(2023, 12, 31)
)
ORDER BY 'Date'[Month]Now, each measures return the identical consequence:
I examined the efficiency of each capabilities for a similar calculation (Rolling complete over 25 days), and the outcomes have been equal. There was no distinction in efficiency or effectivity between these two.
Even the execution plan is similar.
Which means that DATEINPERIOD() is a shortcut operate for DATESBETWEEN().
Conclusion
From a performance standpoint, each of the proven capabilities are nearly equal.
The identical applies from the efficiency standpoint.
They differ in the way in which the top date is outlined.
DATESINPERIOD() is predicated on calendar intervals, like days, months, quarters, and years.
This operate is used when the date vary have to be calculated based mostly on the calendar.
However when we now have a pre-defined finish date or should calculate the date vary between two pre-defined dates, the DATESBETWEEN() operate is the operate to make use of.
For instance, I exploit DATESBETWEEN() when performing Time Intelligence calculations for weeks.
You possibly can learn this piece to study extra about weekly calculations:
As you’ll be able to learn, I retailer the beginning and finish dates of the week for every row within the knowledge desk.
This manner, I can simply lookup every date’s begin and finish dates.
So, once we should choose between these two capabilities, it’s not a matter of performance however of necessities outlined by the stakeholders of the brand new reviews or the wanted knowledge evaluation.
Learn this text to discover ways to accumulate and interpret Efficiency knowledge with DAX Studio:
Like in my earlier articles, I exploit the Contoso pattern dataset. You possibly can obtain the ContosoRetailDW Dataset at no cost from Microsoft here.
The Contoso Knowledge could be freely used below the MIT License, as described in this document. I modified the dataset to shift the info to modern dates.
