Finding Time Between Dates Based on ID

[ehwood]

Hello Experts of the Power BI Community,

Please go easy on me as I'm new with DAX. I hope this isn't a terrible question, but I've been struggling to come up with a solution to my problem.

I have a large table with customer ID's and their order dates. I would like to find the time between each of those dates based on ID, but cannot comeup with a formula to do so. For example, Customer 4 purchased on 1/4, 1/10, and 1/12, so on those 2nd and 3rd orders I want to pull the differences of 6 and 2.

Thanks,

E

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works.  You may download my PBIX file from here.

=if(ISBLANK(CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Customer ID]=EARLIER(Data[Customer ID])&&Data[Date]<EARLIER(Data[Date])))),BLANK(),[Date]-CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Customer ID]=EARLIER(Data[Customer ID])&&Data[Date]<EARLIER(Data[Date]))))

Hope this helps.

[ehwood]

Wow!

That is exactly what I was looking for.Thank you so much for your help!

[Ashish_Mathur]

You are welcome.