Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
qwertzuiop
Advocate III
Advocate III

Date/Time Difference between first and last occurence ID

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

 

qwertzuiop_1-1686034333958.png

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

1 ACCEPTED SOLUTION
Kishore_KVN
Super User
Super User

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!!

View solution in original post

1 REPLY 1
Kishore_KVN
Super User
Super User

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!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.