Room utilization report, need the daily room use delay

[action316]

ID	Date	InRoom	Room	Area
1	9/9/2020	1899-12-30 08:25:18.000	Room 5	ED
2	9/9/2020	1899-12-30 09:28:43.000	Room 5	ED
3	9/9/2020	1899-12-30 10:52:50.000	Room 5	ED
4	9/9/2020	1899-12-30 14:14:22.000	Room 5	ED
5	9/9/2020	1899-12-30 08:27:11.000	Room 6	ED
6	9/9/2020	1899-12-30 08:34:57.000	Room 6	ED
7	9/9/2020	1899-12-30 09:41:37.000	Room 6	ED
8	9/9/2020	1899-12-30 10:58:21.000	Room 6	ED
9	9/9/2020	1899-12-30 14:00:11.000	Room 6	ED
10	9/10/2020	1899-12-30 07:43:46.000	Room 5	ED
11	9/10/2020	1899-12-30 08:08:03.000	Room 5	ED
12	9/10/2020	1899-12-30 09:52:28.000	Room 5	ED
13	9/10/2020	1899-12-30 12:04:18.000	Room 6	ED
14	9/10/2020	1899-12-30 08:03:16.000	Room 6	ED
15	9/10/2020	1899-12-30 10:16:13.000	Room 6	ED

Here is a snippet of my data.  What I need is the minutes delay from the Start Time of 8:00 to the room is use use, InRoom.  Need the minutes  per room, per day, that will roll up and total for day, month, year.  Here is a calculated field that works, but won't total and rollup.  SODD will be one value used to determine room utilization.

SODD = var FirstPatient = CALCULATE (

        MIN ( Sheet1[InRoom] ),

              FILTER (

              ALLEXCEPT ( 'Sheet1', Sheet1[Room] ),

             'Sheet1'[Date]=Max(Sheet1[Date])

)

) var SODD = DATEDIFF(time(8,0,0), FirstPatient , MINUTE ) RETURN IF (SODD > 0, SODD, 0)

My Results end up like this, it's giving me the minimun number instead of the totals.

Wednesday, September 9, 2020  |     25

     Room 5                                   |     25
     Room 6                                   |     27
Thursday, September 10, 2020    |     0
     Room 5                                   |     0
     Room 6                                   |     3

[lbendlin]

Remember that the totals work on the filter context of the entire visual data.  You use MIN() in your calculation, so you get MIN() for the totals.

If you want the sum then calculate the sum , even for each ID, and even if that seems overkill.

What do you want to do for ID 10 ? Just ignore it or use as credit?

Here would be the calculated column for the gap (in hours)

Empty = 24*(max(Occupancy[InRoom],time(8,0,0))-time(8,0,0))

and here are the results

etc.

[action316]

Thanks for the response! I only want the minutes for each day and each room from 8:00 AM to the first time the room is used (InRoom).  That gives me the minutes that the room was available but not used at the begining on the day. I don’t want anything for the other times that the room was used, those can be 0 and if the first time a room is used is before 8:00 it would be 0.

[amitchandak]

@action316 , Something like this?

new column =

var _1  = datediff(time(8,0,0),[InRoom], minute)

return

if(_1>0,_1, 0)