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,
Skilled SharerAnonymous 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.
- GilesWalker
Thanks Anonymous I just finished reading through the information on you link and your idea seems nice and clean. Once I get into work in a couple hours I will try both yours and Sean idea to see what works best.
I will reply back with what I have done.
Giles
GilesWalker I just realized that you asked for the rate, but I gave you the formula for a simple total. But that's ok. Should rate be all rooms / filled rooms or filled rooms / all rooms? Seems like it should be the second. Anyway
All Rooms= DISTINCTCOUNT(TableName[Room_ID])
Occupancy Rate = DIVIDE([Occupancy], [All Rooms])
or
Occupancy Rate = DIVIDE([All Rooms], [Occupancy])
depending on which thing you need divided by the other to get the correct rate.
GilesWalker I think you and I are working on very similar data, though it represents different real-world things. To understand what Sean was suggesting you should probably check out where that solution came from. It was for a similar data set to yours, but it wasn't quite intended to solve the same measure problem you're asking about. You may still find it useful, but you should understand that it isn't a measure. It generates an entire extra table in the data model. You then would need to add the actual measures and relationships needed to get out the numbers you're looking for.