Hi all. I am working to develop a lift analysis in Power BI.
For example... we have a reservation table - each guest's stay will have one record in this table for each night:
ReservationID
Booked Date
Reservation Date
Room Type
GuestID
Rate
We also have a Guest Rooms table which contains all the guest rooms:
RoomID
Room Type
Room Number
I also have a Dates table with attributes like Month Name, etc.
I am able to calculate a measure for the percentage of rooms that are utilized which works well when I pull in the different date attributes. We will filter out conference rooms which are the only room types that are not guest rooms. We want to get utilization:
Utilization = DIVIDE(CALCULATE(COUNT('Reservation'[ReservationID]),FILTER('Reservation','Reservation'[Room Type] <> "Conference")),COUNT('Guest Rooms'[RoomID]),0)
I've also created a booking age column which determines the Age in Months between the booking date and the reservation date:
Booking Age in Months = DATEDIFF('Reservation'[Booked Date],'Reservation'[EntryDate],MONTH)
Using Booking Age In Months with a filter for reservation dates in the past, I can see the percent utilization lift. Example: In April, the hotel was 80% booked. The utilization for each "Booking Age In Months" is as follows:
0 - 21%
1 - 26%
2 - 17%
3 - 8%
4 - 4%
5 - 3%
>6 - 1%
I've done the same for "Booking Age In Weeks" since we would like to see it by week as well.
I want to create a way to see the lift to the current forecasted utilization for a month/week in the future. I need guidance.
How would I go about creating the measure which could calculate this?
Thanks for your help in advance!
