Forum Discussion
Utilization Rate
Hi Hgsilva
to create this we will have to multiple calculated columns and measures.
Step 1: Create Calendar table and create realtionship between Calendar table and Roomdata(sample data) table.
Date=CalendarAuto()Step 2: Create calculated columns for surgery minutes,patient minutes,cleanup minutes and total event minutes
Surg Minutes = DATEDIFF( 'RoomData'[SurgStartDateTime], 'RoomData'[SurgEndDateTime], MINUTE )Patient Minutes = DATEDIFF( 'RoomData'[Patient-InRoomDateTime], 'RoomData'[Patient-OutRoom DateTime], MINUTE )Cleanup Minutes = DATEDIFF( 'RoomData'[Room-CleanUpStartDateTime], 'RoomData'[Room-CleanUpStopDateTime], MINUTE )Total Event Minutes Used = 'RoomData'[Patient Minutes] + 'RoomData'[Cleanup Minutes]Step 3: Create Base Measure for Total Minutes
Total Minutes Used = SUM( 'RoomData'[Total Event Minutes Used] )Step 4: Calculate Total Alloted Minutes Used
Total Allotted Minutes = VAR AllottedPerDay = 600 VAR WorkingDays = CALCULATETABLE( VALUES('Date'[Date]), -- Assumes your Date Table is named 'Date' WEEKDAY('Date'[Date], 2) <= 5 -- Filter for Monday (1) through Friday (5) ) VAR WorkingDaysCount = COUNTROWS( WorkingDays ) RETURN WorkingDaysCount * AllottedPerDayStep 5: Create Utilization Percentage
Room Utilization % = DIVIDE( [Total Minutes Used], [Total Allotted Minutes] )Now customize the logic as per the business requirements in above calculation.
Please give kudos or mark it as solution once confirmed.
Thanks and Regards,
Praful
I use datediff formula to get time between Patient in room time and patient out room time and add that to time difference between clean up start and clean up stop.
The room is column CaseSurgOpLoc and is allotted 600 minutes everyday monday through friday.
| CaseSurgOpLoc | SurgStartDtTm | Patient - In Room Time | Patient - Out Room Time | Room - Clean Up Start | Room - Clean Up Stop |
| UMCCL Ultrasound | 10/22/25 3:45 PM | 10/22/25 3:15 PM | 10/22/25 4:15 PM | ||
| SHOR 02 | 10/22/25 5:44 PM | 10/22/25 5:25 PM | 10/22/25 6:31 PM | 10/22/25 7:00 PM | 10/22/25 7:30 PM |
| UMCCCL 02 | 10/22/25 10:32 AM | 10/22/25 10:23 AM | 10/22/25 10:45 AM | ||
| UMCCCL 02 | 10/22/25 10:32 AM | 10/22/25 10:23 AM | 10/22/25 10:45 AM | ||
| UMCOR 02 | 10/22/25 4:48 PM | 10/22/25 3:58 PM | 10/22/25 8:56 PM | 10/22/25 8:57 PM | 10/22/25 9:08 PM |
| UMCCL Ultrasound | 10/22/25 4:46 PM | 10/22/25 4:26 PM | 10/22/25 5:05 PM | ||
| UMCOR 02 | 10/22/25 7:28 AM | 10/22/25 7:04 AM | 10/22/25 8:11 AM | 10/22/25 8:15 AM | 10/22/25 8:25 AM |
| UMCCCL 02 | 10/22/25 11:32 AM | 10/22/25 11:14 AM | 10/22/25 11:45 AM | ||
| UMCCCL 01 | 10/22/25 11:24 AM | 10/22/25 10:37 AM | 10/22/25 1:50 PM | ||
| UMCCCL 02 | 10/22/25 2:03 PM | 10/22/25 1:43 PM | 10/22/25 2:20 PM | ||
| SHCL TSA | 10/22/25 7:50 AM | 10/22/25 7:10 AM | 10/22/25 8:30 AM | ||
| LD 02 | 10/22/25 1:07 AM | 10/22/25 12:50 AM | 10/22/25 2:18 AM | 10/22/25 2:30 AM | 10/22/25 2:37 AM |
| UMCCL Ultrasound | 10/22/25 2:25 PM | 10/22/25 1:40 PM | 10/22/25 2:50 PM | ||
| SHOR 01 | 10/22/25 4:34 PM | 10/22/25 4:09 PM | 10/22/25 5:48 PM | 10/22/25 5:49 PM | 10/22/25 6:05 PM |
| UMCCL Ultrasound | 10/22/25 12:45 PM | 10/22/25 12:25 PM | 10/22/25 1:10 PM | ||
| UMCEN EUS | 10/22/25 8:25 AM | 10/22/25 8:12 AM | 10/22/25 8:37 AM | 10/22/25 8:38 AM | 10/22/25 8:47 AM |
| UMCEN TSA | 10/22/25 11:43 AM | 10/22/25 11:25 AM | 10/22/25 12:08 PM | 10/22/25 12:09 PM | 10/22/25 12:19 PM |
| EPCHOR 04 | 10/22/25 1:33 PM | 10/22/25 1:21 PM | 10/22/25 1:55 PM | 10/22/25 1:56 PM | 10/22/25 2:04 PM |
| LD 01 | 10/22/25 10:34 PM | 10/22/25 10:20 PM | 10/22/25 11:19 PM | 10/22/25 11:15 PM | 10/22/25 11:30 PM |
Hi Hgsilva
to create this we will have to multiple calculated columns and measures.
Step 1: Create Calendar table and create realtionship between Calendar table and Roomdata(sample data) table.
Date=CalendarAuto()Step 2: Create calculated columns for surgery minutes,patient minutes,cleanup minutes and total event minutes
Surg Minutes = DATEDIFF( 'RoomData'[SurgStartDateTime], 'RoomData'[SurgEndDateTime], MINUTE )Patient Minutes = DATEDIFF( 'RoomData'[Patient-InRoomDateTime], 'RoomData'[Patient-OutRoom DateTime], MINUTE )Cleanup Minutes = DATEDIFF( 'RoomData'[Room-CleanUpStartDateTime], 'RoomData'[Room-CleanUpStopDateTime], MINUTE )Total Event Minutes Used = 'RoomData'[Patient Minutes] + 'RoomData'[Cleanup Minutes]Step 3: Create Base Measure for Total Minutes
Total Minutes Used = SUM( 'RoomData'[Total Event Minutes Used] )Step 4: Calculate Total Alloted Minutes Used
Total Allotted Minutes = VAR AllottedPerDay = 600 VAR WorkingDays = CALCULATETABLE( VALUES('Date'[Date]), -- Assumes your Date Table is named 'Date' WEEKDAY('Date'[Date], 2) <= 5 -- Filter for Monday (1) through Friday (5) ) VAR WorkingDaysCount = COUNTROWS( WorkingDays ) RETURN WorkingDaysCount * AllottedPerDayStep 5: Create Utilization Percentage
Room Utilization % = DIVIDE( [Total Minutes Used], [Total Allotted Minutes] )Now customize the logic as per the business requirements in above calculation.
Please give kudos or mark it as solution once confirmed.
Thanks and Regards,
Praful
Thank you very much Praful_Potphode.
Only problem I'm encountering is the Total Allotted Minutes measure is not correct.
For example in March the Total Allotted Minutes for each room should be 12,600 because each room is given 600 minutes per weekday and there are 21 weekdays in March.
How can I fix this?
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
