Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
This question is a little big for a forum - may take a shot at the solution and then show how you might solve. The way you wrote the question it sounds like you want someone to just do this work for you.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |