topic Room utilization report, need the daily room use delay in DAX Commands and Tips

Room utilization report, need the daily room use delay

action316 — Tue, 15 Sep 2020 17:51:01 GMT

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

Re: Room utilization report, need the daily room use delay

lbendlin — Tue, 15 Sep 2020 18:44:11 GMT

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.

Re: Room utilization report, need the daily room use delay

action316 — Tue, 15 Sep 2020 19:46:36 GMT

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.

Re: Room utilization report, need the daily room use delay

amitchandak — Wed, 16 Sep 2020 03:16:08 GMT

action316 , Something like this?

new column =

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

return

if(_1>0,_1, 0)