Time difference between specific rows

[Peter_2020]

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-10-20 6:05 AM	1102	01
7-10-20 6:10 AM	1103	01
7-10-20 6:20 AM	1101	04
7-10-20 6:25 AM	1102	05
7-10-20 6:30 AM	1103	06
7-10-20 6:30 AM	1101	03
7-10-20 6:40 AM	1102	03
7-10-20 6:50 AM	1103	03

And what I want to do is to calculate time difference between rows with key - 04, 05, 06 and next row but with the same RC number. So my expected output is:

DATE/TIME	RC	KEY	TIME DIFFERENCE
7-10-20 6:00 AM	1101	01
7-10-20 6:05 AM	1102	01
7-10-20 6:10 AM	1103	01
7-10-20 6:20 AM	1101	04	0:10
7-10-20 6:25 AM	1102	05	0:15
7-10-20 6:30 AM	1103	06	0:20
7-10-20 6:30 AM	1101	03
7-10-20 6:40 AM	1102	03
7-10-20 6:50 AM	1103	03

* colors marked which rows are calculated

Thank you for your help in advance.

P

[Peter_2020 · Accepted solution]

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]

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

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