Forum Discussion
Finding Time Between Dates Based on ID
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
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.
3 Replies
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.
Wow!
That is exactly what I was looking for.Thank you so much for your help!
You are welcome.
