Forum Discussion

orobbsh's avatar
orobbsh
New Member
3 years ago
Solved

Difference between 2 times

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's avatar
    v-yinliw-msft
    3 years ago

    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.

2 Replies

  • philouduv's avatar
    philouduv
    Icon for Resolver III rankResolver III
    3 years ago

    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

  • v-yinliw-msft's avatar
    v-yinliw-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.