# How To Calculate Difference In Days Between Purchases Using DAX In Power BI

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.

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

Cheers!

Sam

## How To Compare Two Lists Of Calculated Data Virtually – An Advanced DAX Technique

When working with calculated data, comparing different data sets will sometimes be necessary. I'm going...

## Heat Map – A Great Visualization For Power BI Reports

In this tutorial, youâ€™ll learn how to create a heat map visual using Charticulator. It is used to...

## CALCULATE Function – How It Can Affect Your Calculations On Power BI

In this tutorial, I want to show you what the CALCULATE function can do through a few examples....

## Remove Empty Columns In Power BI

This blog will demonstrate how to automatically remove all empty columns in Power BI through the Power...

## DAX Calculation Groups – Power BI Report for Problem Of The Week #7

I'm going to talk about the solution I came up with for the 7th Problem of the Week. The problem...

## Publishing PowerApps Applications and Changing The Screen Order

In this tutorial, we're going to talk about publishing PowerApps applications and making sure the...

## Power BI Financial Reporting: Allocating Results To Templates At Every Single Row

Here I want to showcase a unique idea around financial reporting, which is allocating results to...

## Optimizing Queries For A Faster DAX Performance

This tutorial will talk about optimizing your queries in DAX Studio. You'll also learn how to mitigate...

## Format Data In Power BI: Addressing Irregular Data Formats

In today's blog post, we'll discuss Problem of the Week #6. I'll show you how to format data in Power...

## Power BI P&L Statements: Challenges And Solutions

Profit and Loss Statements are often challenging to create in Power BI, especially if you're working...

## How To Install DAX Studio & Tabular Editor In Power BI

In this tutorial, you'll learn how to download and install DAX Studio and Tabular Editor 3 in your...