Date/Time Difference between first and last occurence ID

[qwertzuiop]

Dear Power BI Community

Hopefully with this visualisation I can better represent my desired output.

We are looking for the date/time difference of an ID from the first to the last Occurence

It is not necessarily important that the output is in the corresponding line. What is important is that somewhere there is information that, for example, ID MDY5MP takes approx. 3,999 days from first to lasttime (maybe best approach in a measure, no?)

Any ideas how to solve this problem?

Thank you very much for your contribution.

Cheers

qwertzuiop

[Kishore_KVN · Accepted solution]

Hello @qwertzuiop , you have to group it by Member ID then you can do that. 

Use below measure to get the result as required. 

Duration = 
Var Start_Date = CALCULATE(MIN(Data[CS_Date]),ALLEXCEPT(Data,Data[CS_MessageID]))
Var Last_Date = CALCULATE(MAX(Data[CS_Date]),ALLEXCEPT(Data,Data[CS_MessageID]))
Var Result = DATEDIFF(Start_Date,Last_Date,DAY)
Return 
Result

Result will be in days.

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!