Date Differences from Different Rows in a Table

DAX DAX Calculations
1

Hi.
My task seems quite simple but logically complex, I think.
I will explain with the image below.

image
image469×613 28.5 KB

I want to get the time between one patient leaving the theatre room and the next one entering the theatre room. To get this information, I need the difference between Time Out of Theatre and Time Into Theatre. However, the columns are both on different rows as highlighted in red on the image above, e.g:
11:45:00 - 12:30:00 will give me the time the theatre room was free before the next patient walked in.
How do I solve this issue, please?
Note that Theatre Code stands for theatre room so the calculation would have to factor that in too.
I have attached a file
Time Event.csv (489.0 KB)
Thank you

2

Hi @upwardD,

Sounds like a job for PQ :wink:, see if this is meets your requirement.

image
image948×516 21.8 KB

Here’s your sample file:
eDNA - Time difference.pbix (73.1 KB)

I hope this is helplful

5 Likes
3

Hi @upwardD, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

4

Thank you @Melissa for always coming to my rescue. Very much appreciated.

5

@upwardD

Just glad I could help, all the best.