Forum Discussion

GRN_VPR8's avatar
GRN_VPR8
Frequent Visitor
2 years ago
Solved

Finding the Time difference between two events on different rows

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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
GRN_VPR8
Frequent Visitor
2 years ago

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