DATEBETWEEN() in Power BI: As Aggregation Over Time

Posted by

September 21, 2026

On March 18, 2026

Introduction

Aggregations over time, using DATEBETWEEN() in Power BI, are crucial for creating accurate time intelligence calculations. Accurate date handling is essential whether you’re comparing annual performance, examining monthly trends, or developing unique time-based metrics. The DAX function DATEBETWEEN() in Power BI is especially useful for returning a range of dates between a defined start and end date. When combined with CALCULATE(), it allows you to build dynamic time-based calculations tailored to specific reporting needs.

In this blog, you will learn how the DATEBETWEEN() function supports time intelligence in Power BI and enables precise aggregation over time.

Learn more about DAX, check out our blog DAX in Power BI: As a formula language

What is Time Intelligence ?

Time intelligence enables you to manipulate data using fixed time intervals, such as days, months, quarters, and years. Time intelligence functions in DAX require a date table inside the data model to slice and dice the data.

Date TABLE PROPERTIES for Time Intelligence

For the smooth working of time intelligence functions in Power BI:

The calendar date table must be marked as the date table.
The date column should have only one date per row.
All dates should be present with no gaps.
The dates should be complete from 1st of January to 31st of December.
If your date column also contains time, remove or separate the time component.
If the report only references fiscal years, then the date table must include all the dates from the first to the last day of a fiscal year.

Aggregations Over Time Using DATEBETWEEN()

Here, you will learn how to aggregate values over time, starting from the 1st of January and end 31st of December, using the DAX function DATEBETWEEN().

Using DATEBETWEEN()

DATESBETWEEN() returns a table of dates that fall between a given start and end date. Usually, it is frequently used in conjunction with functions like CALCULATE(), MAX(), etc., to filter data dynamically. Also, it is especially helpful for creating custom date ranges in computations, such as total sales in a particular month or quarter.

It has the following syntax:

DATESBETWEEN() returns a table containing a column of dates that is in between a specified range.

Note: Learn more about MAX() in our blog Basic DAX Functions: MIN(), MAX() and AVERAGE()

Step-by-step Tutorial

First, let’s create a code with a fixed start date range using DATE().

Right-click on All Measures in the Fields.
Click on New measure.

Measure tools open.

3. Here, type the following formula in the formula bar and press Enter.

Custom YTD using Variables (CM) =
VAR LastDateofSales =
    MAX ( ‘Date'[Date] )
RETURN
    CALCULATE (
        SUM ( Sales[Sales Amount] ),
        DATESBETWEEN (
            ‘Date'[Date],
            DATE ( YEAR ( LastDateofSales ), 1, 1 ),
            LastDateofSales
        )
    )

In this expression:

DATESBETWEEN filters the date range from the start of the year to the last available sales date, so the measure calculates the difference in sales over that period (YTD) instead of a simple total like Total Sales Amount (CM)
The last date in the current filter context is calculated using MAX().

4. In the formatting section, click on the “$”

5. Then, type “2” in the text box.

6. Click on the matrix visual in the visualization pane.

7. Drag and drop the Year, Month Name, and Date column from the Date table to the rows.

Notice that an automatic date hierarchy is created.

8. Then, drag and drop Total Sales Amount (CM) and Custom YTD using Variables (CM) from the All Measures table to the Values.

Notice that the first transaction was recorded in July 2017, and the Custom YTD using Variables (CM) starts calculating the aggregation from July onwards. Due to the incoming filter context of months on the rows, the value for each month is calculated and displayed.

Note: You can practice the above tutorial on the exercise file given at the end of the blog, or check out our course, Power BI DAX Essentials.

Conclusion

DATESBETWEEN() in Power BI not only enhances the precision of your reports but also strengthens your comprehension of DAX time intelligence, allowing you to develop flexible and scalable analytical solutions. Furthermore, the DATESBETWEEN() function is also helpful when standard time intelligence functions don’t satisfy certain reporting requirements because it offers a dependable and adaptable method of defining custom date ranges.

FAQs

What is DATESBETWEEN() in Power BI?

DATESBETWEEN() creates a table of dates between a given start and end date. To filter data for custom time ranges, it is frequently used in conjunction with functions such as CALCULATE().

Do I need a date table to use DATESBETWEEN()?

Yes, a date table must be set to conduct proper calculations. There must not be any gaps in your date table; it will need to include all dates. Additionally, you must configure your date table as a date table within your model.

How is DATESBETWEEN() different from standard time intelligence functions?

Unlike other functions, DATESBETWEEN() allows you to define custom date ranges, which makes it useful when reporting requirements cannot be met by standard time intelligence functions alone.

Further Learning and Resources

To learn more about Power BI, check out our courses here:

Want to learn how Microsoft 365 Copilot transforms everyday work with AI? Check out our Course:

Microsoft 365 Copilot Training Courses

Want to learn more about Power BI and how to make reports more efficiently?

Power BI training in Australia: We offer comprehensive training sessions across various cities, including :

Power BI Crash Course eBook: Download our detailed guide to take your Power BI expertise to the next level, covering topics from basics to advanced with practical examples and step-by-step instructions. (Limited time offer)  

Download the Beginner’s Guide to Power BI

Ready to enhance your Power BI skills? Sign up for our training courses and explore our wide range of resources designed to elevate your data analytics capabilities. 