Room utilization

[davidv64]

I have a problem which I can solve without too much difficulty in Excel, but since Excel's formulas are row-based and DAX is not, I am struggling with how to solve this in DAX or PowerQuery. 

The situation is that we have operating rooms which are used from 7am until 5 pm. That is, there are 600 minutes available for use by surgeons. I am trying to calculate two things. First is the turnover time which is defined as the number of minutes from the time one patient leaves the room until the next patient is in the room. Assume that one surgeon has one room per day.  The second question is what percentage of the 600 minutes which are available are actually used during the day by one surgeon? 

In the attached spreadsheet, I provided a sheet with Raw Data and then a sheet with finished data, using excel functions to calculate the various outcomes I desire (columns "Turnover Time", "Block efficiency" and "Block utilization". Block efficiency and block utilization are essentially the same except that block utilization includes the turnover time. 

Eventually, I want to produce a KPI which shows the Block Utilization, mean turnover time and Block Efficiency for each surgeon filtered by a date slicer. 

I would appreciate any help on solving this using DAX or PowerQuery. 

Attached is my data.

https://1drv.ms/x/s!AodJ9zqlTT5LsZlfTW1dZ1za7Fw6Ng?e=VE3q92

[davidv64]

Thank you very much!

[davidv64]

Thank you for your time. Your solution will take me a little while to make sense of. May I ask what are DQ issues?  Also, I need to sort these data by surgeon and by month. 

[lbendlin]

DQ = Data Quality.  You have duplicate entries for 2/21.

[lbendlin · Accepted solution]

See attached. Note that your sample data has DQ issues.