Forum Discussion

davidv43's avatar
davidv43
Regular Visitor
5 years ago

calculate time difference between two rows

I am trying to write a function which calculates the time from when one case in an OR finishes and the next case starts. Attached is a sample spreadsheet which lists an OR resource, the start and sto...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

davidv43 , Based on what I got so far. Based on each OR resource

 

a new column

 


new column =
var _max = maxX(filter(Table, [OR Resource] =earlier([OR Resource]) && [Patient in room] <earlier([Patient in room])),[Patient Out room])
return
datediff(_max, [Patient in room], second)

  • davidv43's avatar
    davidv43
    Regular Visitor
    5 years ago

    For the most part, this works, although I don't understand the "earlier" syntax. The MIcrosoft DAX guide says:

    EARLIER ( <ColumnName> [, <Number>] )

    PARAMETER ATTRIBUTES DESCRIPTION

    ColumnName 

    The column that contains the desired value.

    NumberOptional

    The number of table scan.

    Return values

     

    SCALAR A single value of any type.

     

    The current value of row, from ColumnName, at Number of outer evaluation passes.

     

    Also, I need to make sure that when the day changes, the turnoever time is not calculated for the first case of the day. 

     

    I have a lot to learn about DAX!  Thank you for this help!