Forum Discussion

oksasquatch's avatar
oksasquatch
Regular Visitor
4 years ago
Solved

Report on Meeting Room utilization

Hello all, I have a powershell script that provides meeting room usage over a period of time. I am looking to build a Power BI report that will give us a % utilization for week/month/etc... This is...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi oksasquatch 

     

    You can break down the process of building a report. First you need to create a date table, you can refer to the following blog. It introduces DAX and Power Query method. You can choose one to do that. Or you can also prepare a date table in other data sources like CSV file and Excel file, and import it into this Power BI report. 

    https://radacad.com/power-bi-date-or-calendar-table-best-method-dax-or-power-query 

     

    Currently do you only have the CSV output table? If you want to calculate the meeting room usage for every room, you need to prepare a table which has all rooms in it. For example, you can create a table similar to below. Ensure that all rooms are included. 

    No. Room Name
    1 Room1
    2 Room2
    3 Room3
    ...... ...

     

    And when you calculate the meeting room usage, do you need to consider weekends and holidays? If so, you need to add a column "Is Working Day" in the Date table to tell which dates are working days and which are not. You can use 1 to represent working day and use 0 for non-working day. 

     

    After preparing above tables, you need to consider which visuals or charts you want to use to display your results. And what results are used in which charts. You probably need to create measures to calculate the results, so we need to know which results are expected. Then we can write possible DAX codes to get them.

     

    Best Regards,
    Community Support Team _ Jing

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

oksasquatch , How many hours room available each day

 

 

You can create a table like this

Available = Addcolumns(generate(distinct(Table[Room]), calendar(date(2020,1,1) , date(2022,12,31) )), "Hour Daily", 12)

 

With a common room Table

Room = distinct(Table[Room])

 

calendar table

calendar(date(2020,1,1) , date(2022,12,31) )

 

Join these two with the new table and you table

 

and the you can hours in you table 

hour = datediff([StartDatetime], [Enddatetime], hour) // a new column

 

%utilize  = divide(sum(Table[Hour]) , Sum(Available[Available]) )

  • oksasquatch's avatar
    oksasquatch
    Regular Visitor
    4 years ago

    Thank you, we are going to use 12 hours for availability for each day. Would you have time to walk me through setting up the tables? I am pretty new to Power Bi.