Calculate Rolling Totals Using DATESBETWEEN In Power BI


Here I’m going to show you how you can calculate the amount sold between two different dates using the DATESBETWEEN function in Power BI. You may watch the full video of this tutorial at the bottom of this blog.

Another way to think about this is calculating rolling sales or running totals.

We need the calculation to be dynamic, so that as we move through time, we have a number that re-calculates based on the time frame window that we open.

In this blog, I show you how on any single day, you can isolate two dates and count up the amount sold between those two dates. This is ultimately how you calculate running totals in Power BI.

power bi datesbetween

I’m going to dive in how you can do that with DAX formula, specifically the DATESBETWEEN function in Power BI. I’m not going to stop there, as I also want to compare running totals between different time periods.

Rolling Totals Using The DATESBETWEEN Function

There are a couple of ways that you could do this, but in this tutorial, I want to showcase how to use the Power BI DATESBETWEEN function.

This time intelligence function is incredibly flexible in terms of the logic you can put inside it to then create or calculate quickly a time-related calculation in a very unique situation.

In this example, we are looking at rolling sales of the current year, and then we compare it to the previous year. The darker green line shows the rolling sales with 30 days, and we’re comparing that to the year before.

power bi datesbetween

This would be perfect if you wanted to identify trends, for example, or inventory because inventory levels are a really good one to use for rolling calculations.

On this table below, you see that we’ve branched out and determined the difference. If you could identify quite a big difference in the trend here, then that would certainly tell you something, and perhaps will help you in your business decisions.

power bi datesbetween

Now how I did this is the key to achieving these insights.

Calculations Using DATESBETWEEN

In this technique, we place the DATESBETWEEN inside a CALCULATE statement, but we have two parameters here that we can work with. First, we put in the Dates column just to recognize what columns look down.

Then, by these two parameters (MAX Dates), we can actually create any two dates or any time period between these two dates that we want. In other words, we could put absolutely anything in here.


Here, the MAX (function) Dates brings up the current date minus 30 (days, as we have set here). It opens up a 30-day window every day, and looking from today backwards for 30 days, then counting up total sales.

So with this formula using the DATESBETWEEN function in calculating 30-day sales, what we’re doing is that we start from today and open up every single day back 30 days, and then just sum up the total.

Furthermore, with this formula, we could go easily into something else, say rolling 90 days instead of 30. All we have to do is change a few parameters here. Then, we can branch out into different time-frame calculations for this technique.

power bi datesbetween

Now let’s look at another formula that uses DATESBETWEEN, which allows us to calculate the cumulative sales quarterly.

power bi datesbetween

This might be a bit complicated for something that can be done easily, but I just want to show you that we can actually put anything in this formula. We can put FIRST NONBLANK or LAST NONBLANK, or anything that returns a date. And then, open up or create a date window or a time frame to calculate whatever core measure we want to, in this case, it’s Total Sales.

***** Related Links *****
How To Calculate A Cumulative Run Rate In Power BI Using DAX
Running Totals in Power BI: How To Calculate Using DAX Formula
Time Intelligence In Power BI: How to Calculate The Number of Transactions Made in the Last N Days


I hope that with this tutorial, you become familiar with the DATESBETWEEN function in Power BI because there’s a lot of really great applications for it.

What we’re doing in DATESBETWEEN is that we are creating a context between any two dates that we specify. There’s a variety of different applications of any calculation that we might want to do between two dates.

This is a great way to identify trends in your data or showcase outliers that may be occurring in real time in your datasets.

There are lots of techniques to extract out of this one tutorial, as well as plenty to learn about the DAX formula and how to implement it successfully. Jump in and learn as much as you can. Try to implement this in your own models ASAP to really get the most benefit out of it.

All the best,


Enterprise DNA Power BI On-Demand

18 comments on “Calculate Rolling Totals Using DATESBETWEEN In Power BI”

  1. Does the rolling number you set take into account missing days like weekends or days where there may not be any sales? For instance, if you were looking at a Tuesday and did a rolling 7, would it calculate sales for Tuesday, Monday, Sunday, Saturday, Friday, Thursday, Wednesday? Or does it just look at the dates that are part of your Sales data?

    1. Hi Rose, you are correct, it would look at all the dates. To adjust this to only look at weekdays or days where there is a sale some different logic would need to be used. It’s an interesting concept your propose. Something I’ll look into showcasing shortly.

  2. How does it work under the hood? A flowchart would be illuminating. For me, understanding the WHY is key to remembering a technique. Presently, it’s just a code snippet I know works but don’t know why.

    I’m also curious why you’re using DATESBETWEEN and not DATESINPERIOD; is it more efficient? The tutorial I’m going thru now has this code using DATESINPERIOD. He also didn’t do much about explaining the WHY so I started searching. Actually, the “MAX” is what confused me most but now I realize I don’t know what it’s really doing. I tried re-writing it using FILTER iterator and it failed miserably, thereby illustrating my utter lack of understanding the concept, and this tutorial only improved that marginally.

    m_10 Day Rolling Revenue = CALCULATE([m_TotalRevenue], DATESINPERIOD(AW_Calendar_Lookup[Date],max(AW_Calendar_Lookup[Date]),-10,DAY))

    Side-note: A 28-day “Rolling Total” makes more business sense, or 7-day or 14-day. That way it doesn’t gigger up-n-down when weekends play a role. Less of an issue for big date periods, but something to think about where small % changes can influence business decisions.

    1. Hi Brad,

      Thanks for your interest in Enterprise DNA Blogs.

      Both DATESBETWEEN and DATESINPERIOD can be used to perform Rolling Totals. The difference is primarily in the Syntax.

      Regarding max(AW_Calendar_Lookup[Date]), it returns the Max Date in terms of current Evaluation context. When using AW_Calendar_Lookup[Date]) as Axis in Line Chart or Table, it will return the current date on each row.

      Similarly if using Month on Axis, Max will return the last (max) date of the each month.

  3. Hi, everything assumes that there is 1 line per transaction, but I work with project so they start one day and end on another day in the future and I have in the same line the billing rate per day. I need this billing rate to roll for every day of the project. and then with the aggregation of all the projects I would get my total potential billings. I am using this formula at the moment to make the daily rolling and then I am using visual filters to exclude saturday and sunday.

    sum for daily = CALCULATE(sumx(FILTER(‘AWGS- Offshore’,’AWGS- Offshore'[Start Date]=max(Date_Master[Date]))),([GP Daily sum])))

    But I cant get it to aggregate by week. My table has week of the year, end of the week…etc…all imaginable combinations and breakdowns. Any help would be appreciated

  4. Hi Sam,

    Could you please share the DAX code for Rolling 30 Day LY calculation you have used in your video??

    I am actually trying to calculate for 12 month rolling average for current and prior year and stumbling on the Prior year calculation.

    1. Hi Sumanta Das,

      Thanks for your interest in EDNA blogs.

      For Rolling 30 Day LY calculation, first create Sales LY and then use it in Running total measure.

      Total Sales LY = CALCULATE([Total Sales],SAMEPERIODLASTYEAR(Dates[Date]))

      30d Running Total – LY =
      CALCULATE( [Total Sales LY],
      MAX( Dates[Date] ),
      DAY ) )

  5. If I sign up on your website, can we download the sample file used on the page? I would like to see more detail on the Rolling 30 Day LY. I would also like to see how you overlaid the rolling and prior rolling.

    I have been struggling with showing rolling year vs prior rolling year. I accomplished it by pulling in 2 tables, one with the current rolling, and the other with the prior rolling. On the prior rolling table I adjusted the date up a year. Then I connected both tables to my date table.

    If I can get the file, I will definitely sign up for your training.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.