Forum Discussion
Skilled SharerHotel occupancy rates
Think I've got it solved for you:
Add an index column to your Bookings table, since I'm assuming the GuestID refers to a specific person and that person can book multiple rooms at a time or across a given time period.
Add a Date Dimension.
Your model should look like the following:
Create the following measures:
Rooms Occupied:=CALCULATE(DISTINCTCOUNT(Bookings[Index Column]),Filter(Bookings,[Date_from]<=LASTDATE(DimDate[Date])&&[Date_to]>=FIRSTDATE(DimDate[Date])))
Rooms Available (Total for Selected Dates):=CALCULATE(DISTINCTCOUNT(DimDate[Date]))*48
Total Dates Booked:=CALCULATE(DISTINCTCOUNT(DimDate[Date]),Bookings)
Total Rooms Occupied:=SUMX(Bookings,[Rooms Occupied]*[Total Dates Booked])
OccupancyRate:=DIVIDE([Total Rooms Occupied],[Rooms Available (Total for Selected Dates)])
Or
OccupancyRate:=DIVIDE([Total Rooms Occupied],[Rooms Available (Total for Selected Dates)])*DIVIDE([Rows in Bookings],[Rows in Bookings])
Pivot Table Examples:
Please mark it as a solution or give a kudo if it works for you, otherwise let me know if you run into an issue and I'll do my best to assist. Go To bipatterns.com for more techniques and user guides.
Thanks,
Thanks for your replies GilesWalker and Ashish_Mathur, they are highly appreciated.
I have attemted to use provided formula to create a measure, but it throws the error I have experienced with other attempts to create this measure:
'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.'
I might be applying it in a wrong way, but it also seems like the question of how my data table looks like as pointed by Ashish_Mathur can be of use.
My initial data table contains rows for each reservation per (berth) room, customer, product, start date and end date.
| Id | Berth__c | End_Day__c | Start_Day__c | Products__c | AccountID |
| a4Jb0 | a4Kb0 | 16-06-16 | 15-06-16 | a7jb0 | |
| a4Jb0 | a4Kb0 | 23-09-17 | 22-09-17 | a7jb0 | |
| a4Jb0 | a4Kb0 | 23-09-17 | 22-09-17 | a7jb0 | |
| a4Jb0 | a4Kb0 | 24-09-17 | 23-09-17 | a7jb0 | |
| a4Jb0 | a4Kb0 | 20-06-16 | 15-06-16 | a7jb0 | |
| a4Jb0 | a4Kb0 | 15-08-17 | 13-08-17 | a7jb0 | |
| a4Jb0 | a4Kb0 | 03-08-17 | 02-08-17 | a7jb0 | |
| a4Jb0 | a4Kb0 | 16-06-16 | 15-06-16 | a7jb0 |
Obviously all of the values appear many times, as the dataset contains few years of reservations.
Edit: what I am trying to get is the occupancy by month and length for instance, the example of occupancy length for one period is presented below. Columns Count and Area as well as length of berths are added into Data table from related tables. The primary question is how to find the Occ count based on Data table.
| Length | Count | Area | Occ Count | Occ Area | Occ% Count | Occ% Area |
| 6 | 8 | 144 | 6.40 | 115.20 | 80% | 80% |
| 7 | 10 | 210 | 8.00 | 168.00 | 80% | 80% |
| 12 | 30 | 1,440 | 24.00 | 1,152.00 | 80% | 80% |
| 13.5 | 20 | 1,080 | 16.00 | 864.00 | 80% | 80% |
| 15 | 30 | 2,250 | 24.00 | 1,800.00 | 80% | 80% |
| 17 | 20 | 1,700 | 16.00 | 1,360.00 | 80% | 80% |
| 18 | 10 | 1,080 | 8.00 | 864.00 | 80% | 80% |
Thanks in advance and let me know if I can help you with any additional information
Edin
- Ashish_Mathur
Super User
Hi,
I do not understand how you have computed the numbers in the Occ Count column. Please explain in detail.
My apologies for not being clear enough.
That information comes from a different table. But as I said the focus is on Occupied Berths Count and that's the only bit of information to be calculated based on the presented data table.
I hope it makes more sense now.
Thansk a lot!Edin
- Ashish_Mathur
I still do not understand. How have you computed the numbers in the Occ Count column? How did you arrive at the numbers - 6.4,8,24 etc?
