Difference between 2 times

[orobbsh]

HI 

i want to find the difference between 2 time in separate rows like this table

 

i want the result to be like this : 

[v-yinliw-msft · Accepted solution]

Hi @orobbsh , 

You can try this method:

Create two columns:

var =
CALCULATE (
MAX ( 'Table'[end] ),
FILTER ( 'Table', 'Table'[surgery id] = EARLIER ( 'Table'[surgery id] ) - 1 ))

difference = MINUTE('Table'[start] - 'Table'[var])

New tables:

Result =
TOPN (
4,
SELECTCOLUMNS (
'Table',
"Surgery",
IF (
'Table'[surgery id] <= 3,
"S" & 'Table'[surgery id],
IF ( 'Table'[surgery id] = 5, "S4" )
),
"start time", 'Table'[start],
"end time",
IF (
'Table'[surgery id] < 3,
[end],
IF (
'Table'[surgery id] = 3
|| 'Table'[surgery id] = 4,
CALCULATE (
MAX ( 'Table'[end] ),
FILTER ( 'Table', 'Table'[surgery id] = 3 || 'Table'[surgery id] = 4 )
),
TIME ( 12, 0, 0 )
)
),
"time difference", 'Table'[difference]
),
[Surgery], DESC
)

The result is:

Hope this helps you.

Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

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

[philouduv]

Hello @orobbsh ,
I do not know if it feasible in your situation but if you delete the first cell of start (8:00 AM) and that you shift all cells in the start column 1 cell up, you would only need to create a new column calculating the difference between start and end to have the time difference without jugling with different lines.

Best regards