About Calculating Date Ranges in DAX

When performing date calculations, creating date ranges can be helpful. But how can we do this, and which DAX function can help us in which case? Now you can learn more about this topic. The post About Calculating Date Ranges in DAX appeared first on Towards Data Science.

May 22, 2025 - 20:40

Introduction

When developing Time Intelligence Measures with Power BI or in Fabric in Semantic Models, it can be necessary to create a date range to calculate the result for a specific time frame.

To be precise, almost all of the Time Intelligence functions in Dax create a list of dates for a date range.

But sometimes we must create a custom date range due to specific requirements.

DAX offers us two functions for this task:

Both functions take a Start Date as a parameter.
But for the End Date, the behavior is different.

While DATESINPERIOD() takes Intervals (Days, Months, Quarters, Years), DATESBETWEEN() takes a specified Date used as the End Date.

In contrast, DATEADD() uses the current Filter Context to get the Start Date and to calculate the End Date.

But we want to pass a Start Date, which can differ from the Date(s) in the current Filter Context.

This is when one of the functions mentioned above comes into play.

At the end of this article, I will show you a practical example using the techniques shown here.

Tools and scenario

Like in many other articles, I use DAX Studio to write DAX Queries and analyze the results.

If you are not familiar with writing DAX queries, read my piece on how to learn to write such queries:

How to Write Queries for Tabular Models with DAX

This time, I use the Data model only for the Date table.

I want to calculate a date range starting from May 5. 2025 and either 25 days or 2 Months into the future.

To set the start date, I use this expression:

DEFINE
    VAR StartDate = "2025-05-05"

EVALUATE
       { StartDate }

This is the result in DAX Studio:

Figure 1 – Query and result in DAX Studio (Figure by the Author)

I define a Variable and assign the result of the date expression for the subsequent queries.

Another way to define the start date is to create a date value using DATE(2025, 05, 05).

The result will be the same.

The difference between these two approaches is that the first returns a string, but the second returns a proper date.

The DAX functions used here can work with both.

Using DATESINPERIOD()

Let’s start with DATEINPERIOD().

I will use this function to get a date range string from the Start Date and 25 days into the future:

DEFINE
    VAR StartDate = "2025-05-05"
    
EVALUATE
    DATESINPERIOD('Date'[Date]
                    ,StartDate
                    ,25
                    ,DAY)

The result is a table with 25 rows for the days starting from May 05, 2025, to May 29, 2025:

Figure 2 – 25 Days calculated with DATESINPERIOD() (Figure by the Author)

Now, let’s slightly change the query to get a list of all dates from the Start Date to 2 Months into the future:

DEFINE
    VAR StartDate = "2025-05-05"
    
EVALUATE
    DATESINPERIOD('Date'[Date]
                    ,StartDate
                    ,2
                    ,MONTH)

The query returns 61 rows starting from Max 05, 2025, until July 04, 2025:

Figure 3 – 2 Months of dates generated with DATESINPERIOD() (Figure by the Author)

I can pass the interval with an arbitrary number of days (e.g., 14, 28, 30, or 31 days), and the function automatically calculates the date range.

When I pass negative numbers, the date range goes to the past, starting with the start date.

Using DATESBETWEEN()

Now, let’s look at DATESBETWEEN().

DATESBETWEEN() takes a Start- and an End-Date as parameters.

This means I must calculate the end date before using it.

When I want to get a date range from May 05 to May 29, 2025, I must use the following query:

DEFINE
    VAR StartDate = "2025-05-05"
    
    VAR EndDate = "2025-05-25"
    
EVALUATE        
    DATESBETWEEN('Date'[Date]
                    ,StartDate
                    ,EndDate)

The result is the same as with DATESINPERIOD().

However, there is one crucial point: The end date is included in the result.

This means I can write something like this to get a date range over two months from May 05 to July 05, 2025:

DEFINE
    VAR StartDate = "2025-05-05"
    
    VAR EndDate = "2025-07-05"
    
EVALUATE        
    DATESBETWEEN('Date'[Date]
                    ,StartDate
                    ,EndDate)

The result is very similar to the one using DATESINPERIOD() and month as the interval, but with one row more:

Figure 4 – Result for a date range over two months, plus one row (Figure by the Author)

This gives me more flexibility to create the date ranges, as I can pre-calculate the end date according to my needs.

Use in Measures – a practical example.

I can use these methods to calculate a running total in a Measure.

But we must take care to use the two functions in the right way

For example, to calculate the running total per month for 25 days.

Look at the following code, where I define two Measures using the two functions:

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]
        ,"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]

This is the result:

Figure 5 – Result of the running total over 25 days with the two functions (Figure by the Author)

Notice the difference between the two results.

This is because DATESBETWEEN() includes the end date in the result, while DATESINPERIOD() adds the number of intervals to the start date but includes the start date.

Try it out with the following query:

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 first returns 25 rows (May 05 – May 29, 2025) and the second returns 26 rows (May 05 – May 30, 2025).

Therefore, I must change one of the two Measures to get the same result.

In this case, the calculation definition is: Start from the first date and go 25 into the future.

The corrected logic is 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 instead of 25 days
        
        RETURN
            CALCULATE ( [Sum Online Sales]
                        , DateRange )

EVALUATE
CALCULATETABLE (
    SUMMARIZECOLUMNS (
        'Date'[Year]
        ,'Date'[Month]
        ,"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, both measures return the same result:

Figure 6 – Result of the corrected Measures (Figure by the Author)

I tested the performance of both functions for the same calculation (Rolling total over 25 days), and the results were equal. There was no difference in performance or efficiency between these two.

Even the execution plan is the same.

This means that DATEINPERIOD() is a shortcut function for DATESBETWEEN().

Conclusion

From a functionality point of view, both of the shown functions are almost equivalent.

The same applies from the performance point of view.

They differ in the way the end date is defined.

DATESINPERIOD() is based on calendar intervals, like days, months, quarters, and years.
This function is used when the date range must be calculated based on the calendar.

But when we have a pre-defined end date or must calculate the date range between two pre-defined dates, the DATESBETWEEN() function is the function to use.

For example, I use DATESBETWEEN() when performing Time Intelligence calculations for weeks.

You can read this piece to learn more about weekly calculations:

Advanced Time Intelligence in DAX with Performance in Mind

As you can read, I store the start and end dates of the week for each row in the data table.

This way, I can easily look up each date’s start and end dates.

So, when we must select between these two functions, it’s not a matter of functionality but of requirements defined by the stakeholders of the new reports or the needed data analysis.

References

Read this article to learn how to collect and interpret Performance data with DAX Studio:

How to Get Performance Data from Power BI with DAX Studio

Like in my previous articles, I use the Contoso sample dataset. You can download the ContosoRetailDW Dataset for free from Microsoft here.

The Contoso Data can be freely used under the MIT License, as described in this document. I changed the dataset to shift the data to contemporary dates.

The post About Calculating Date Ranges in DAX appeared first on Towards Data Science.