calculate Occupancy rate based on start & end time

[joep78]

Hi all,

I'm currently working on a solution to get insight into the Occupancy rate of our meeting rooms, based on start & end time. My challenge is that if i'm only using my start date, my reservation is only count in that specific hour. If a reservations is more then one hour, the following hour it seems to be available. 

data (simple example) is as follow:

room	date	start time	en time
1.39	01/05/2020	10:00:00	12:00:00
1.40	01/05/2020	11:00:00	13:00:00
1.42	01/05/2020	09:00:00	15:00:00

the outcome should be as follow:

time	amount of reservations in this timeslot
09:00:00	1
10:00:00	2
11:00:00	3
12:00:00	2
13:00:00	1
14:00:00	1
15:00:00	1

How should I take the end time also into account in my measure so I know the room is reserved if the end time is not exceded?

Thanks in advance for a reply!

Regards,

Joep

[AllisonKennedy]

@joep78  I am unable to send private message right now, so in response to your query to be able to filter the occupancy data by meeting room location, etc: 

You'll need to create it as a measure not a column.

I have created 'key' columns in the Power Query Editor for each of your date time fields simply because I find that Power BI does funny things with DateTime data type when trying to relate them to each other.

In the Power Query Editor, the key columns can be created using the Add Column > Custom Column and the formula: 

=DateTime.ToText([End Date],"yyyyMMddHHmm")

replace [End Date] with the DateTime columns in for both start and end datetime in the meeting table and also for datetime in the datetime table.

Close and load the changes, then in the report, create the following MEASURES: 

Occupied = CALCULATE(COUNT(Meetings[Room]),FILTER(Meetings,Meetings[StartKey]<=SELECTEDVALUE('Time'[Key])

&&Meetings[EndKey]>SELECTEDVALUE('Time'[Key])

))

Average Daily Occupancy = CALCULATE(AVERAGEX('Time',[Occupied]))

The [Occupied] measure will only work if you include the exact date and time in your table/matrix visualization, so the [Average Daily Occupancy] allows you to summarize the data across all days or just specific days of the week. 

Since it's a measure, any filters you put on Location, Level, etc should carry through to the table/matrix you use the measure in.

Does that make sense? Give kudos if it works. 

[joep78]

The issue now with the measure Occupied  is that it only shows a value during the start time, but no value between start time and end time. in case a reservation starts at 08:00 AM until 02:00 PM, it now only is occupied on 08:00 AM and not occupied the next hours till 02:00 PM. my measure now is as follow (based on my original table/column names):

Occupied = CALCULATE(COUNT(BaseOrder[SpaceRef]),FILTER(BaseOrder,BaseOrder[StartKey]<=SELECTEDVALUE('DimDate'[Key])

&&BaseOrder[EndKey]>=SELECTEDVALUE('DimDate'[Key])

))

[AllisonKennedy]

This happens because of the relationship between the time and meetings table. Try putting the BaseOrder table inside an ALL() to ignore that relationship for this calculation like this:

Occupied = CALCULATE(COUNT(BaseOrder[SpaceRef]),FILTER(ALL(BaseOrder),BaseOrder[StartKey]<=SELECTEDVALUE('DimDate'[Key])

&&BaseOrder[EndKey]>=SELECTEDVALUE('DimDate'[Key])

))

[TomMartens]

Hey @joep78 ,

can you please elaborate on why room 1.42 will be marked as reserved at 3:00 PM, at least I think this is the room being responsible for the reservation mark at 15:00:00.

From the endtime I would have deduced that this room will be available from 3:00 PM onwards once again.

Regards,

Tom

[joep78]

Hi Tom,

sunday morning data mistake, 15:00 shouldn't have any reservations open indeed. Thanks for the remark.

[AllisonKennedy · Accepted solution]

Do you have a separate date/time table? If not, you'll need to create one with each hour listed for each day. If you need help with this, reply and I can provide more details. Assuming you already have the date/time table:

Use the following formula to add a column to the date table:

Occupancy =
COUNTROWS (
FILTER (
Meetings,
AND (
Meetings[Start Time] <= DimTime[Time],
Meetings[End Time] >= DimTime[Time]
)
)
)

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[joep78]

Thanks, adding the Time to the DimDate table and your column solved my issue! Thank you!

[joep78]

Hi Allison,

I do have a DimDate table but no [time] within. Is there an easy way to add this to this DimDate table. Browser the internet was not helpful so perhaps you are able to help me out with this?

[AllisonKennedy]

Here's one to get you started, I've added Day of week, Time and hour, but you can add/remove any of those columns. You may also change the start and end dates. I have set them to start 1 Jan 2020 until now, the now being dynamic: 

let
startDate = #datetime(2020, 1, 01, 00, 00, 00),
endDate = DateTime.LocalNow(),
Dates = List.DateTimes(startDate, Duration.Days(endDate - startDate)*24, #duration (0,1,0,0)),
#"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date Time"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date Time", type datetime}}),
#"Inserted Day of Week" = Table.AddColumn(#"Changed Type", "Day of Week", each Date.DayOfWeek([Date Time]), Int64.Type),
#"Inserted Hour" = Table.AddColumn(#"Inserted Day of Week", "Hour", each Time.Hour([Date Time]), Int64.Type),
#"Inserted Time" = Table.AddColumn(#"Inserted Hour", "Time", each DateTime.Time([Date Time]), type time)
in
#"Inserted Time"

Paste that code into the Advanced Editor of a new query in the Power Query Editor.

Then, you will just need to combine the Date and Start Time in your Meetings table as [Start Date] and combine the Date and End Time in your meetings table as [End Date] (you can do this in the Query Editor in the Add Column tab).

Use this formula in DAX as a calculated column for the Time table (same as formula in my previous post but with updated column names): 

Occupancy = COUNTROWS(FILTER(Meetings,AND(Meetings[Start Date]<=Time[Date Time],Meetings[End Date]>=Time[Date Time])))

Again, let me know if you require clarification on any of the above.

Note: This is a Time table table, NOT a date table. This link might help to explain why it is not a date table. https://docs.microsoft.com/en-us/power-bi/desktop-date-tables