Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

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

by | 9:00 am EDT | June 24, 2020 | 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 Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## How to Interpolate in Excel: User Guide With Examples

In data analysis, interpolation plays a crucial role in estimating values that fall between known data...

## Funny ChatGPT Prompts: 20 Hilarious ChatGPT Ideas

In a world where technology continues to amaze us, we have now arrived at the point where we can have a...

## Power BI Slicer Search: User Guide With Examples

Ready to get started with the Power BI slicer? This feature will allow you to filter and slice your...

## SUMPRODUCT Multiple Criteria: Explained With Examples

Most Excel users think that the SUMPRODUCT function in Excel helps only to multiply the numbers in...

## Data Analytics Outsourcing: Pros and Cons Explained

In today's data-driven world, businesses are constantly swimming in a sea of information, seeking the...

## How to Embed Power BI in Sharepoint: 4 Simple Steps

Embedding Power BI reports in SharePoint Online is a powerful way to display interactive data...

## The Top 5 Power BI Alternatives in 2023

Power BI has established itself as a powerful business analytics platform, offering a wide range of...

## Power BI Waterfall Chart: A Detailed User Guide

In the world of data visualization, charts speak louder than numbers. If you're looking for a way to...

## Power BI Import vs Direct Query: Which is Better & Why?

In the world of data analysis, Power BI offers you a range of tools to connect to your data sources....

## Power BI Certification: Everything You Need to Know

In today's data-driven world, the ability to transform raw numbers into meaningful insights is more...

## Power BI Bookmarks: The Ultimate Guide

When working with data, bookmarks offer a streamlined and personalized way to navigate through large...