topic Difference between 2 times in DAX Commands and Tips

Difference between 2 times

orobbsh — Wed, 30 Nov 2022 13:39:30 GMT

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

i want the result to be like this :

Re: Difference between 2 times

philouduv — Wed, 30 Nov 2022 14:39:25 GMT

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

Re: Difference between 2 times

v-yinliw-msft — Thu, 01 Dec 2022 05:34:12 GMT

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.