Need help with two columns for days between dates

[seanpratt291]

I need two help with two columns here - hoping it's not a major issue. 

Essentially what I need to do is understand two things

1) What is the time between the same product being purchased by the same user? That is "Days Since Last Purchase of this product". For the Purchase # of 1, it's null - but for the second purchase it's the days between purchase #2 and purchase #1. The same logic applies down the column, but it'll reset for each Customer. I'm using this to determine for products that have repeat purchases, what's the average days before a purchase is made again on that same product.

2) Very similar logic but it's simply for order #. I just want to understand the average days between repeat customers placing an order, regardless of what they're ordering.

I hope that's clear. This is the current set up of my table and the two columns on the right are what I need either help with or a workaround solution!

Thank you!

[Ashish_Mathur]

Hi,

Let's only look at the result in the second last column.  In that column, what result are you expecting for row 4(Order ID 772).  should the result be [Nov 2 - Oct 15] or [Nov 2 - Sep 27].  Share your expected result in a simple MS Excel file so that your Excel formulas can be translated into the DAX language.

Also, do you want a calculated column formula solution or a measure solution?

[seanpratt291]

Hi there,

Here's the link to it
https://www.dropbox.com/scl/fi/anohgfg9wja61zo7osri2/Book1.xlsx?dl=0&rlkey=k8wid6bq03ur79jtab7j1obq7

Essentially I need the second last column to always be the time between the current order date and the LAST order date, not the FIRST order date.

The same logic applies to the very last column, but instead of using the 'Days Since Last Purchase' column, you use the "Days Since Last Order"

[Ashish_Mathur · Accepted solution]

Hi,

These calculated column formulas work

Days Since Last Purchase of Product = if(ISBLANK(CALCULATE(MAX(Data[Order Date]),FILTER(Data,Data[Customer ID]=EARLIER(Data[Customer ID])&&Data[Purchase #]<EARLIER(Data[Purchase #])))),BLANK(),Data[Order Date]-CALCULATE(MAX(Data[Order Date]),FILTER(Data,Data[Customer ID]=EARLIER(Data[Customer ID])&&Data[Purchase #]<EARLIER(Data[Purchase #]))))

Days Since Last order = if(ISBLANK(CALCULATE(MAX(Data[Order Date]),FILTER(Data,Data[Customer ID]=EARLIER(Data[Customer ID])&&Data[Order #]<EARLIER(Data[Order #])))),BLANK(),Data[Order Date]-CALCULATE(MAX(Data[Order Date]),FILTER(Data,Data[Customer ID]=EARLIER(Data[Customer ID])&&Data[Order #]<EARLIER(Data[Order #]))))

Hope this helps.

[seanpratt291]

Amazing! That worked perfectly 🙂 Thank you!

[Ashish_Mathur]

You are welcome.