Forum Discussion

Peter_2020's avatar
Peter_2020
Helper III
5 years ago
Solved

Time difference between specific rows

Hello all,  I would like to ask for help with calculation of time difference between specific rows in one table. I have this kind of data:   DATE/TIME RC KEY 7-10-20 6:00 AM 1101 01 7...
Need Help
  • Peter_2020's avatar
    Peter_2020
    5 years ago

    Finally I found solution. For those whom it can help:

     

    Time Difference Measure =
    VAR NextRow =
    CALCULATE (
    MIN ( Table[DATE/TIME]), FILTER ( ALLEXCEPT ( Table , Table[RC] ), Table[DATE/TIME] > SELECTEDVALUE ( Table[DATE/TIME])))
     
    RETURN
    IF (
    SELECTEDVALUE ( Table[Bit] ) = 1,
    DATEDIFF ( SELECTEDVALUE ( Table[DATE/TIME] ), NextRow, SECOND ))
     
    BIT is measure which returns 1 for KEY - 04,05,06.

     

amitchandak's avatar
amitchandak
Super User
5 years ago

Peter_2020 , Try a new column like

if([Key] in {"04","05","06"}, [Date/time], Maxx(filter(table,[RC] =earlier([RC]) && [Date/time] <earlier([Date/time])),[Date/time]), blank())

 

But I doubt you are doing diff with the same RC number

Peter_2020's avatar
Peter_2020
Helper III
5 years ago

amitchandak  Thanks for your help but it doesn´t work. I forgot to mention that I am using DirectQuery mode.