Forum Discussion
Helper IHow to Create Visuals for Occupancy rate for Rental Business (my source files included)
Hi all, and thanks for the good help I have received so far. I have made big strides since my last question, and now have all income/expense data in nice visuals in PowerBi.
The last step for me would be to visualize the data I have to see when in the year my occupancy rates are highest and also how many guests we host during the year. I receive this data in comma separated CSV files where the cleaned output looks like this (the file is included).
Step1
First step I need to do is to get the listing name identified with my dimension table dim_properties (data included in the Excel file). How do I best do this to avoid having to manually add the property ID in the Excel source file? Can this be done in the transformation process in PowerBI. For instance Transform->Add column named "Property ID"-> replace "Sansel * Cabin with "pid2".
Step2
I need suggestions and help on how to visualize the data files included in this post in PowerBI.
The solution I went with was by following this video: https://www.youtube.com/watch?v=ISDhR-TzwJk
By doing this I was able to create a new table where all the days occupied was listed, and then I could make my visuals. For instance the following one:
5 Replies
tormiPartly a bump but also an example that would be great, even if I could only achieve some of it like a monthly occupancy rate overview with a slicer to show per property.
https://www.atharvasystem.com/powerbi-hotel-occupancy-revenue-analytics-dashboard/
- lbendlin
Super User
You can do a property ID lookup via a custom column generator
= Table.AddColumn(#"Changed Type", "Property ID", (k)=> Table.SelectRows(Properties, each Text.StartsWith(k[Listing Name],[Property Name]))[Property ID]{0})Your sample bookings data has a Listing name
Bjørgegrend 69 that is not in the properties table - you will want to correct that.
Next step will be to decide how you want to show the occupancy in the calendar. One way would be to explode the [From Date]/[To Date] interval into individual rows that you could then tie to the calendar table. Of course you would need to explain how to handle guest overlaps (departing/arriving on the same day).
How do you define "occupancy rate"?
see attached
- tormi
Definition of occupancy rate: Days of stay per guest per property
Handling of check-in/out the same day:
Guest A checks in 28.11.22 and out 2.12.22
Guest B checks in 2.12.22 and leaves 10.12.22
Guest A has stayed 3 days in november and 1 day in december (I don't count the check-out day for the guest)
Guest B stays for 9 days in December
Does this make sense?
The matrix you setup looks good, I will be excited to see what other ways to visualize this ways. A yearly month calender with color coding of the percentage of bookings would be sexy. 🙂
Hoper this made sense.
- tormi
I finally got to look at your pbi file now lbendlin . You summed up the guest numbers, but what I am after is the number of days per month/year the different properties have been occupied. The number of guest data I am not sure wether I will keep. (It could be interesting to see the average number of guest per stay per property.)
- tormi
The solution I went with was by following this video: https://www.youtube.com/watch?v=ISDhR-TzwJk
By doing this I was able to create a new table where all the days occupied was listed, and then I could make my visuals. For instance the following one:
