You’ll learn in this tutorial how to calculate difference in days between purchases using DAX in Power BI. This topic came from the Enterprise DNA support forum and I’ll show you how I solved it. This is an interesting scenario and quite common in the business world. You may watch the full video of this tutorial at the bottom of this blog.
In this example, we’re looking at a customer who purchased many times and we want to see the difference between the days of purchase.
The key is to simplify your DAX formulas.
I always recommend keeping your formulas short and simple by utilizing the measure branching technique and using and combining the correct DAX functions. You don’t really need complex formulas in your model. One technique that I use for complex scenarios is variables (VAR), which I’ll demonstrate it in this tutorial.
Sample Data Set
First, let’s look at the set up of the model. This is just a demo data set that I’ve created. In this case, we’ll focus on the Purchase Date column. The one thing that we need to make is a Numeric Index Column because we’re going to work through this column to make the logic simpler.
So we want to be able to put on any filter here (customer, product, location, etc.) and work out the difference between the last date that the customer purchased and the prior purchase date. In this example, we have a filter on location, which is dynamic. We can click through the location IDs and the data will change and show us the results of whatever location we select.
We see in this table the order ID, customer ID, purchase date of the customers in a specific location, and the difference in days between their purchases. The other thing to note here is that when there are two purchases on one day, the second date should be zero because it’s no different to the prior day.
DAX Formula To Calculate The Difference Between Purchases
Now let’s look at the formula. A lot of variables are utilized here, which I highly recommend when you have a little bit more logic to think through. It’s just so much easier to audit your numbers later on and understand the logic that’s happening.
Note that every result here is calculated individually. So at every single result or row in this table, we need to work out what these variables are calculating. For example, this 7th of the 8th 2016 with the result of 12 (days between the last day of purchase, which was the 26th of July).
To get this result, we start with the index number (IndexNum), which is 430 in this case, using MAX. Then, we compute for the previous index number (PreviousIndexNum) by calculating the same thing (MAX of Index), but we change the context in which we calculate that using a FILTER function inside of CALCULATE.
Next, we calculate the current date (CurrentDate) using VALUE and SELECTEDVALUE. The current date in this example is the 7th of the 8th of 2016. Our last variable is the prior date (PriorDate), which we can easily figure out because we already have the PreviousIndexNum.
The second part of the formula, which is the IF statement, simply uses the variables we created and specifically identifies the first date as 0. Then, we subtract CurrentDate from PriorDate to get the 12 days difference.
And that’s how you calculate difference in days between purchases in Power BI.
***** Related Links*****
Last Purchase Date In Power BI: When Did Your Customers Make Their Last Purchase?
How To Work With Multiple Dates In Power BI
Calculate Rolling Totals Using DATESBETWEEN In Power BI
Conclusion
Hopefully you can see how measure branching is at work here. We started with one variable (IndexNum), and then use it to calculate the next one, and so on.
I really enjoyed working through this one and I’m sure that this is going to help many people. It’s a common scenario where you need to calculate difference in days. It could be between purchases or any event.
If you want to learn more about this topic, check out the video and the links below.
Cheers!
Sam
***** 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
