Find Prior procedure start time (surgery) for the same room same Doctor

[D_ellsworth]

I am trying to find the prior procedure (surgery) end time for the same room, Same day, Same surgeon same surgical area. I am new to LookML and eager to learn but having a hard time converting my DAX measures to LookML formulas. Any help is appreciated. Thanks!

[Rhountu]

Hey Hi Buddy, we would need more information to provide any insights. If you could provide the table schema, that would be great.

[D_ellsworth]

Hello Rhountu,

Thanks for reaching out for clarification.

All the data is in one report. The applicable column headers are as follows

Surgical area / Operation Room / Date of surgery / Procedure start time / Surgeon name / Procedure end time. 

I've accomplished this a few different ways in dax and excel. First I use a formula to assign sequence of cases for that Operating room for that specific day. Then I use a I have set up key that covers the room name (CRM OR 11 or SJ Endo 12) a specific 6 digit number then the second part of the key is the date and the last part is the case sequence. I then set up another column that subtracts one from that key column and then I have a lookup key. That is the round about excel way. The dax way is much more straightforward but I'm not at my desktop currently. 

Thanks for the help & community on this

[jbernal]

If I'm understanding correctly you could get the data and sort it by area,room,day,surgeon, time (or whatever order makes sense) etc. If sorted right that would assume that for any one surgery the row above it is its previous surgery in the same room/area/same surgeon etc. In order to get that previous row's value in the current row you could use the offset function (see docs for that function here).

(Very rough) example where the table calculation shows the previous rows completion for the user. Notice that if the above row is another user's data then it will not give you that previous row's completion date. It only shows it when it's the same user.