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,
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
Skilled Shareredinvz - 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 )
)