Forum Discussion

Hgsilva's avatar
Hgsilva
Frequent Visitor
9 months ago
Solved

Utilization Rate

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?

Need Help
  • Praful_Potphode's avatar
    Praful_Potphode
    9 months ago

    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

     

10 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    9 months ago

    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

     

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support
    9 months ago

    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.

    • Hgsilva's avatar
      Hgsilva
      Frequent Visitor
      9 months ago

      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
      • lbendlin's avatar
        lbendlin
        Super User
        9 months ago

        Always fun to show the graphical solution

         

         

         

        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.

         

  • Hgsilva's avatar
    Hgsilva
    Frequent Visitor
    9 months ago

    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
    • Praful_Potphode's avatar
      Praful_Potphode
      Super User
      9 months ago

      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

       

      • Hgsilva's avatar
        Hgsilva
        Frequent Visitor
        9 months ago

        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?