Forum Discussion
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 an example of the CSV output fomr the powershell. Would someone be able to help me walk through the process of building a report? I have some experience with basic reports but I am only able to sum the totals for the different rooms right now.
There are a total of 8 rooms for this test...if this works we will implement across the tenant.
Thanks in advance!!!
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
3 Replies
v-jingzhangCommunity Support
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
Super User
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]) )
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.
