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

 
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. 

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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