calculate time difference between two rows

[davidv43]

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 stop times for a case and the surgeon. I need to automatically calculate the time from when a surgeon completes one case until the next case starts (turnover time). Data are sorted by OR resource first and then time/date from earliest to latest. 

• I calculated turnover times manually in Sheet 1, column G for the first few records, to let you see what I am trying to do
• The first case in the room for that day does not have a turnoever time
• Regardless of whether the surgeon changes rooms, the turnover time is from one surgeon starting a case until the same surgeon starts another case in that same room (It's rare for another surgeon to squeeze in a case between two other cases
• I also want to report the number of cases each surgeon performs in each room by date (see Sheet 2 in the workbook)

For a beginning in DAX formulas, this is a very difficult problem for me, though it is conceptually simple. Any help is very greatly appreciated! Below is a link to my sample data.

https://1drv.ms/x/s!AodJ9zqlTT5LqoM9ba4tzmrRABdigw?e=vi6cqE

Best!

Dave

[amitchandak]

@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]

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.
Number	Optional	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!