Forum Discussion
Skilled SharerHotel occupancy rates
Hi everyone,
I have been stuck on this issue for a few days ad am probably over thinking things, so I am hoping someone out there can provide a tidy solution.
I have created some dummy data for a hotel and need to work out the occupancy rates (total rooms / filled rooms). The total rooms is 48, here is the data:
| Room_ID | Guest_ID | Date_from | Date_to |
| 109 | 74 | 1/01/2016 | 6/01/2016 |
| 217 | 85 | 1/01/2016 | 5/01/2016 |
| 210 | 16 | 1/01/2016 | 6/01/2016 |
| 116 | 53 | 1/01/2016 | 5/01/2016 |
| 209 | 129 | 1/01/2016 | 3/01/2016 |
| 214 | 82 | 2/01/2016 | 3/01/2016 |
| 215 | 96 | 2/01/2016 | 3/01/2016 |
| 204 | 13 | 2/01/2016 | 7/01/2016 |
| 108 | 18 | 3/01/2016 | 6/01/2016 |
| 115 | 101 | 5/01/2016 | 8/01/2016 |
| 207 | 12 | 5/01/2016 | 7/01/2016 |
| 104 | 23 | 6/01/2016 | 7/01/2016 |
| 403 | 138 | 7/01/2016 | 12/01/2016 |
| 111 | 15 | 7/01/2016 | 11/01/2016 |
| 404 | 131 | 7/01/2016 | 12/01/2016 |
| 106 | 95 | 7/01/2016 | 12/01/2016 |
| 304 | 95 | 8/01/2016 | 9/01/2016 |
| 302 | 17 | 8/01/2016 | 13/01/2016 |
| 212 | 109 | 9/01/2016 | 12/01/2016 |
| 302 | 4 | 9/01/2016 | 14/01/2016 |
Its a fairly straight forward table. However my issue is if I want to work out the occupancy rate on a day, week, month, or year how to get a dax formula to calculate the days inbetween the date_from and date_to. I have thought it could be done with total number of days in the relevant time frame, however would be interested to see the communities ideas.
The report has a date key with a continuous dates and all the relevant weeks, months, etc. This is linked via the Date_from column.
Thanks,
Giles
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,
31 Replies
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,
GilesWalkerAnonymous - thanks for you help with this your solution worked as it takes into account the total available days for rooms and then uses the actual count of time a room is booked.
sdjensen - thanks for your assistance with this, you were spot on in that you have to take into account the days available compared to days booked out per room.
Anonymous - thanks for all the help with this and for clarifying what the measures were doing.
- GilesWalker
Anonymous - Having some trouble this query again, turned out the numbers were not calculating correctly and part of the formula you gave worked with a bit of tweaking to give me the total days booked. To calculate the total days booked I am using this:
Inclusive days booked = SUMX(
FILTER(Bookings, Bookings[Date_from] <= LASTDATE (DateKey[Date]) && Bookings[Date_to] >= FIRSTDATE (DateKey[Date])),
COUNTROWS(DATESBETWEEN (DateKey[Date],Bookings[Date_from],Bookings[Date_to])))
The issue I run into is with the reporting over time periods. If I have a room booked out from 30 January to 4 February that means that in January there were two days booked, and in February there were four days booked. However the above formula says booked for six days which is correct however it allocates those six days to the month i have the datekey relationship linked to (i.e if the date_from is in January then January gets the six days). I have tried changing the DateKey table relationship to be linked to either Date_from and Date_to in the bookings table, it makes no difference.
Is there a filter that can be used so that when filtering by a month/week/quarter etc. a measure will calculate only the days in the month. In this example January would have two days allocated (30 and 31 January), and February would get four days (1-4 February).
Heres a picture of what I am currently dealing with:
Thanks,
Giles
- Yggdrasill
Responsive Resident
Any idea how to fill in the blanks ?
E.g. there should be 5 on 7th and 9 through out September...- Ashish_Mathur
Super User
Hi,
Explain the question and share the data (in a format that can be pasted in an Excel file)
This thread has a couple of different approaches to that problem. http://community.powerbi.com/t5/Desktop/Holiday-Calculation-From-Start-and-End-Date-to-quot-Aggregation/m-p/42343
This is a pretty interesting request. If you have a dummy excel file available, I'd be happy to provide a solution. Attach it here if possible. In order to help I'll need a better understanding of the data model in this case.
- GilesWalker
Anonymous happy to, how do I do that?
GilesWalker So that you don't have to wade through that other thread to pick the best of the offered solutions, I'd recommend the following:
First, that relationship between Date_from and the date table. There should be no table relationships for this to work. Again, see that other thread for the details. Then here's your occupancy measure:
Occupancy = CALCULATE(
DISTINCTCOUNT(TableName[Room_ID]),
FILTER(
TableName,
TableName[Date_from] <= LASTDATE(DateTable[Date]) &&
TableName[Date_to] >= FIRSTDATE(DateTable[Date])
)
)You can place any time period from the date table next to this measure and it will work. A month, a week, a single day... I have it counting each room only once, but you may want to do a distinctcount of guests instead if you want to count the same room multiple times if it is occupied multiple times in a period. Or you could do a regular count of rooms instead of a distinctcount. Depends on your specific needs.
Dear Power BI pros :-)
I have tried to crack this one for some time and still cannot manage to get what I need.
I want to get the measure that would calculate the number of occupied rooms each day, but also getting the average number of rooms occupied when aggregated to monthly and annual basis.
I have solved it by creating the Calculated Column using the measure [Rooms Occupied] provided by rdurkin and then averaged it. It works just fine for total occupancy, but wait does not allow for slicing and dicing by different segments e.g. product, room type, size and similar.
I would appreciate if anyone could give a try to suggest a solution for the problem outlined above.
And just in case it makes difference, I am working in Excel Power Pivot, rather than Power BI.
Thanks in advance and looking forward to see how genius Dax measures can be.
Edin
- GilesWalker
edinvz - I managed to find a solution to this and have used it a few times and it does work well:
Daily Table =
FILTER (
CROSSJOIN ( Table1, CALENDARAUTO () ),
Table1[date_from] <= [Date]
&& [Date]
<=
VAR EndDate = Table1[date_to]
RETURN
IF ( EndDate = BLANK (), TODAY (), EndDate )
) - Ashish_Mathur
Hi edinvz,
Share the link from where i can download your Excel workbook and also the expected result there.
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
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
Not sure if the problem is too complex or I confused you.
If anyone is willing to help I will provide more details.
Edin
