Days between orders of Unique Customers

[jeroenwmwillems]

Hi there,

I'm getting stuck with a query and haven't been able to find any answers in previous posts from other users. 

Effectively, I have 2 columns of interest: Date and UserID. I'm trying to calculate the amount of days in between consequtive orders for the individual users. I've made a screenshot of an example file with column 3, days between orders, the desired column to make. (In my actual data, these values are of course not perfectly fitlered like this). 

Thanks in advance for your wisdom!

Cheers,

Jeroen

[Anonymous]

Hi @jeroenwmwillems

Please follow these steps:

1.Create a new calculation column and write

Days between orders = DATEDIFF(calculate(max('Table'[Date]),filter('Table','Table'[UserID]=earlier('Table'[UserID])&&'Table'[Date]<earlier('Table'[Date]))),'Table'[Date],DAY)

2.The results obtained are as follows

 

If your Current Period does not refer to this, please clarify in a follow-up reply.

^{Best Regards,}

^{Clara Gong}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[Ashish_Mathur]

Hi,

Write this calculated column formula

Days between orders = Data[Date]-calculate(max(Data[Date]),filter(Data,Data[UserID]=earlier(Data[UserID])&&Data[Date]<earlier(Data[Date])))

Hope this helps.

[jeroenwmwillems]

Thanks heaps for this Ashish, this did exactly what I asked. 

I discovered I made a mistake in my logic however, as I am actually trying to see how many days it took for a customer to order again after a certain event. Therefore, my end result should ideally look like this:

Would there be a way of tweaking the fomula to get this as a result? 

Thanks again for your help!

[Ashish_Mathur · Accepted solution]

Hi, You are welcome. Try this =Days between orders = calculate(min(Data[Date]),filter(Data,Data[UserID]=earlier(Data[UserID])&&Data[Date]>earlier(Data[Date])))-Data[Date] Hope this helps.

[jeroenwmwillems]

Thanks Ashish, incredible!

[Ashish_Mathur]

You are welcome.