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 - 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
GilesWalker For any of the solutions I've suggested there should be no relationship at all between Bookings and DateKey. Any relationship will break these durational measure patterns. From there you should be able to use this:
Inclusive Days Booked = SUMX( FILTER( Bookings, Bookings[Date_from] <= LASTDATE(DateKey[Date]) && Bookings[Date_to] >= FIRSTDATE(DateKey[Date]) ), CALCULATE( DISTINCTCOUNT(DateKey[Date]), DATESBETWEEN( DateKey[Date], Bookings[Date_from], Bookings[Date_to] ) ) )
This only works sensibly if you only have one room selected at a time, or if you do something like a clustered bar chart with room_ID as the legend. Otherwise the same day will be counted multiple times, once for each room that is occupied during the time period. But your screenshot looks like it's only for one room at a time anyway.
I'm fairly certain that putting a CALCULATE inside a SUMX is some sort of DAX sin but I can live with that.