Finding the Time difference between two events on different rows

[GRN_VPR8]

Hi, I have a table (an example of this table below as a real one is hundreds of thousands of records with more columns) as detailed below, and I am after a way of finding out the duration between two events. So, I want a way to look for the earliest event_time for one TM_ID and then look for the next event time for the tm_id and, in the duration column, tell me the difference.

I have populated the duration column with how I want it to work.

event_time		tm_id		duration
11/09/23 12:47:20.000		123456
11/09/23 12:49:57.000		456789
11/09/23 14:50:20.000		123456		00:03:00.000
11/09/23 13:00:00.000		456789		00:01:03.000

[Anonymous · Accepted solution]

Hi @GRN_VPR8 ,

We can create calculated columns.

Rank = RANKX(FILTER('Table','Table'[tm_id]=EARLIER('Table'[tm_id])),'Table'[event_time],,ASC,Dense)

Column = 
var _a= MINX(FILTER(ALLSELECTED('Table'),'Table'[tm_id]=EARLIER('Table'[tm_id]) && 'Table'[Rank]=EARLIER('Table'[Rank])-1),[event_time])
var _b='Table'[event_time]
var _c=DATEDIFF(_a,_b,SECOND)
return IF(_a=BLANK(),BLANK(),_b-_a)

Best Regards,

Neeko Tang

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

[Anonymous]

Hi @GRN_VPR8 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a calculated column.

Rank = RANKX(FILTER('Table','Table'[tm_id]=EARLIER('Table'[tm_id])),'Table'[event_time],,ASC,Dense)

(3) We can create a measure.

duration = 
var _a= MINX(FILTER(ALLSELECTED('Table'),'Table'[tm_id]=MAX('Table'[tm_id]) && 'Table'[Rank]=MAX('Table'[Rank])-1),[event_time])
var _b=MAX('Table'[event_time])
var _c=DATEDIFF(_a,_b,SECOND)
return IF(_a=BLANK(),BLANK(),
(INT(_c / 3600) & ":" &
RIGHT("0" & INT((_c - INT(_c / 3600) * 3600) / 60), 2) & ":" &
RIGHT("0" & MOD(_c, 3600), 2)))

(4) Then the result is as follows.

Best Regards,

Neeko Tang

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

[GRN_VPR8]

I was looking for a solution to have it as a new column.

The issue with your solution is shown as text and won't change format; the seconds are more than 1 Min. 

Thanks for replying anyway 

[GRN_VPR8]

@amitchandak  I see you might have done a similar solution to the above. Can you help?