Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
Hgsilva
Frequent Visitor

Utilization Rate

‎10-22-2025 02:34 PM

Good afternoon,

 

I'm have trouble building a formula for utilization of rooms in a building. Each room is alloted 600 minutes a day, monday through friday. I have two collumns that added together total the amount a specific customer spent in room. 

 

How can I get the total amount of minutes each room is used per day and per month?

Labels:
Message 1 of 11
5,145 Views
0
1 ACCEPTED SOLUTION
Praful_Potphode
Solution Sage
Solution Sage
In response to Hgsilva

‎10-23-2025 11:44 AM

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 * AllottedPerDay

Step 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

 

View solution in original post

Message 8 of 11
5,013 Views
10 REPLIES 10
Hgsilva
Frequent Visitor

‎10-23-2025 08:32 AM

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.  

 

 

 

CaseSurgOpLocSurgStartDtTmPatient - In Room TimePatient - Out Room TimeRoom - Clean Up StartRoom - Clean Up Stop
UMCCL Ultrasound10/22/25 3:45 PM10/22/25 3:15 PM10/22/25 4:15 PM  
SHOR 0210/22/25 5:44 PM10/22/25 5:25 PM10/22/25 6:31 PM10/22/25 7:00 PM10/22/25 7:30 PM
UMCCCL 0210/22/25 10:32 AM10/22/25 10:23 AM10/22/25 10:45 AM  
UMCCCL 0210/22/25 10:32 AM10/22/25 10:23 AM10/22/25 10:45 AM  
UMCOR 0210/22/25 4:48 PM10/22/25 3:58 PM10/22/25 8:56 PM10/22/25 8:57 PM10/22/25 9:08 PM
UMCCL Ultrasound10/22/25 4:46 PM10/22/25 4:26 PM10/22/25 5:05 PM  
UMCOR 0210/22/25 7:28 AM10/22/25 7:04 AM10/22/25 8:11 AM10/22/25 8:15 AM10/22/25 8:25 AM
UMCCCL 0210/22/25 11:32 AM10/22/25 11:14 AM10/22/25 11:45 AM  
UMCCCL 0110/22/25 11:24 AM10/22/25 10:37 AM10/22/25 1:50 PM  
UMCCCL 0210/22/25 2:03 PM10/22/25 1:43 PM10/22/25 2:20 PM  
SHCL TSA10/22/25 7:50 AM10/22/25 7:10 AM10/22/25 8:30 AM  
LD 0210/22/25 1:07 AM10/22/25 12:50 AM10/22/25 2:18 AM10/22/25 2:30 AM10/22/25 2:37 AM
UMCCL Ultrasound10/22/25 2:25 PM10/22/25 1:40 PM10/22/25 2:50 PM  
SHOR 0110/22/25 4:34 PM10/22/25 4:09 PM10/22/25 5:48 PM10/22/25 5:49 PM10/22/25 6:05 PM
UMCCL Ultrasound10/22/25 12:45 PM10/22/25 12:25 PM10/22/25 1:10 PM  
UMCEN EUS10/22/25 8:25 AM10/22/25 8:12 AM10/22/25 8:37 AM10/22/25 8:38 AM10/22/25 8:47 AM
UMCEN TSA10/22/25 11:43 AM10/22/25 11:25 AM10/22/25 12:08 PM10/22/25 12:09 PM10/22/25 12:19 PM
EPCHOR 0410/22/25 1:33 PM10/22/25 1:21 PM10/22/25 1:55 PM10/22/25 1:56 PM10/22/25 2:04 PM
LD 0110/22/25 10:34 PM10/22/25 10:20 PM10/22/25 11:19 PM10/22/25 11:15 PM10/22/25 11:30 PM
Message 7 of 11
5,045 Views
0
Praful_Potphode
Solution Sage
Solution Sage
In response to Hgsilva

‎10-23-2025 11:44 AM

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 * AllottedPerDay

Step 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

 

Message 8 of 11
5,014 Views
Hgsilva
Frequent Visitor
In response to Praful_Potphode

‎10-24-2025 07:57 AM

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?

Message 9 of 11
4,939 Views
0
Praful_Potphode
Solution Sage
Solution Sage
In response to Hgsilva

‎10-24-2025 10:52 AM

Hi @Hgsilva 

Try below measure Total Allotted Minutes

Total Allotted Minutes = 
VAR AllottedPerDay = 600
// VAR max_date=MONTH(MAX('Date'[Date]))
VAR WorkingDays =
    CALCULATETABLE(
        VALUES('Date'[Date]) -- Assumes your Date Table is named 'Date'
        ,YEAR('Date'[Date])=YEAR(MAX('Date'[Date]))
        ,MONTH('Date'[Date])=MONTH(MAX('Date'[Date]))
        ,WEEKDAY('Date'[Date], 2) <= 5  -- Filter for Monday (1) through Friday (5)
    )
VAR WorkingDaysCount = COUNTROWS( WorkingDays )
RETURN    
    WorkingDaysCount 
    * AllottedPerDay

  Please give kudos or mark it as solution once confirmed.

 

Thanks and Regards,

Praful

Message 11 of 11
4,917 Views
0
lbendlin
Super User
Super User
In response to Hgsilva

‎10-24-2025 10:41 AM

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Message 10 of 11
4,925 Views
0
v-ssriganesh
Community Support
Community Support

‎10-23-2025 03:13 AM

Hi @Hgsilva,
To assist you further, could you please provide sample data that clearly illustrates the issue you're experiencing?

  • Include a small dataset that fully captures the issue (preferably in table format or as a downloadable file, not just a screenshot).
  • Avoid including any sensitive or unrelated information.
  • Also share the expected outcome based on the sample data you provide.

Need help preparing or uploading sample data? You can refer to this helpful guide:
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

This will help us reproduce your scenario accurately and provide a precise solution.

Best regards,
Ganesh Singamshetty.

Message 4 of 11
5,057 Views
0
Hgsilva
Frequent Visitor
In response to v-ssriganesh

‎10-23-2025 10:34 AM

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.  

 

CaseSurgOpLocSurgStartDtTmPatient - In Room TimePatient - Out Room TimeRoom - Clean Up StartRoom - Clean Up Stop
UMCCL Ultrasound10/22/25 3:45 PM10/22/25 3:15 PM10/22/25 4:15 PM  
SHOR 0210/22/25 5:44 PM10/22/25 5:25 PM10/22/25 6:31 PM10/22/25 7:00 PM10/22/25 7:30 PM
UMCCCL 0210/22/25 10:32 AM10/22/25 10:23 AM10/22/25 10:45 AM  
UMCCCL 0210/22/25 10:32 AM10/22/25 10:23 AM10/22/25 10:45 AM  
UMCOR 0210/22/25 4:48 PM10/22/25 3:58 PM10/22/25 8:56 PM10/22/25 8:57 PM10/22/25 9:08 PM
UMCCL Ultrasound10/22/25 4:46 PM10/22/25 4:26 PM10/22/25 5:05 PM  
UMCOR 0210/22/25 7:28 AM10/22/25 7:04 AM10/22/25 8:11 AM10/22/25 8:15 AM10/22/25 8:25 AM
UMCCCL 0210/22/25 11:32 AM10/22/25 11:14 AM10/22/25 11:45 AM  
UMCCCL 0110/22/25 11:24 AM10/22/25 10:37 AM10/22/25 1:50 PM  
UMCCCL 0210/22/25 2:03 PM10/22/25 1:43 PM10/22/25 2:20 PM  
SHCL TSA10/22/25 7:50 AM10/22/25 7:10 AM10/22/25 8:30 AM  
LD 0210/22/25 1:07 AM10/22/25 12:50 AM10/22/25 2:18 AM10/22/25 2:30 AM10/22/25 2:37 AM
UMCCL Ultrasound10/22/25 2:25 PM10/22/25 1:40 PM10/22/25 2:50 PM  
SHOR 0110/22/25 4:34 PM10/22/25 4:09 PM10/22/25 5:48 PM10/22/25 5:49 PM10/22/25 6:05 PM
UMCCL Ultrasound10/22/25 12:45 PM10/22/25 12:25 PM10/22/25 1:10 PM  
UMCEN EUS10/22/25 8:25 AM10/22/25 8:12 AM10/22/25 8:37 AM10/22/25 8:38 AM10/22/25 8:47 AM
UMCEN TSA10/22/25 11:43 AM10/22/25 11:25 AM10/22/25 12:08 PM10/22/25 12:09 PM10/22/25 12:19 PM
EPCHOR 0410/22/25 1:33 PM10/22/25 1:21 PM10/22/25 1:55 PM10/22/25 1:56 PM10/22/25 2:04 PM
LD 0110/22/25 10:34 PM10/22/25 10:20 PM10/22/25 11:19 PM10/22/25 11:15 PM10/22/25 11:30 PM
Message 5 of 11
5,031 Views
0
lbendlin
Super User
Super User
In response to Hgsilva

‎10-23-2025 03:29 PM

Always fun to show the graphical solution

 

lbendlin_2-1761260212953.png

 

 

Since you work with minutes you can overlay a reference table (1440 minutes in 24 hrs) with your durations via INTERSECT  to find out the room occupancy per 24 hr period.

 

Message 6 of 11
4,994 Views
Praful_Potphode
Solution Sage
Solution Sage

‎10-22-2025 11:58 PM

@Hgsilva 
Please provide more information to understand it better.

also,please follow below link to understand earlier utilization related queries asked in the forum.

https://community.fabric.microsoft.com/t5/Desktop/Utilization-rate/m-p/1055694 

Thanks and Regards,

Praful

Message 3 of 11
5,070 Views
0
PhilipTreacy
Super User
Super User

‎10-22-2025 05:00 PM

@Hgsilva 

 

Please supply some sample data otherwise anything we create will most likely need to modified to fit your actual data.

 

Plus without your data, I'm just guessing as to what data you have.

 

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Message 2 of 11
5,098 Views

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All