Calculate Rolling Totals Using DATESBETWEEN In Power BI

4 comments

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.

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 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.

power bi DATESBETWEEN

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.

Conclusion

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

Sam

Download

Insert your email address and press Download for access to the files used in this article.

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events

membership banner 3

***** 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

***** Related Course Modules *****
Time Intelligence Calculations
Mastering DAX Calculations
Advanced DAX Combinations

***** Related Support Forums *****
Grand Total (which is correct) Is Not Equal As The Addition Of The Several Items
Sales From A Specific Date
Comparing New vs Lost vs Steady Customers Over Multiple Years – Customer Churn Examples – Advanced DAX
For more DATESBETWEEN queries to review see here…..

Enterprise DNA Events

4 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.

Leave a Reply

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