Hotel 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,

Ryan Durkin