Forum Discussion

davidv43's avatar
davidv43
Regular Visitor
4 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 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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 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
      4 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!