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!