Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello all.
I was previously shown a way to look at a my reservation dataset and find my occupancy or availability. By creating a "stay detail' for every day of a booking I can then see what days have no detail and in turn, find out was is available for any date range. My problem now is being able to tie this back to a Rate table to sum the potential revenue based on the date range for those unoccupied properties. In my rate table every property has a rate for every day of the year. So if property A is open for 7/1/2018 - 7/11/2018 how much revenue can I potentially make.
When I try to map the Rates table to property table to my reservation table or stay detail table, I lose the mapping if no reservation or activity is found. Could it be the date needs to be a separate table and not one from my reservations? How can I, using a date range, filter to show me properties that have no activity for that date range and then sum the rates found on the rate table for those dates for those properties? If needed I can group the dates to a weekly set and use that as my range vs daily.
I tried creating a quick mini version of what the tables look like. If this doesn't help please let me know. Thanks,
Hi @Anonymous,
From your description, I could not figure out the relationships between your ‘Rate’ table, ‘Property’ table and the ‘Reservation’ table. And I want to how could you map the price if no reservation or activity is found?
Would you please clarify the desired results based on your sample data?
Regards,
Daniel He
Hey @v-danhe-msft
thanks for replying.
The best way to describe this:
1. The Reservation, Property, and Rate table all relate to each other through the Property ID.
2. The Reservation Detail Table ties to Reservations through the Reservation ID.
What I am trying to do is look at the activity for a Property and see if there is any remaining opportunity to make money. If a property does not have any reservations or reservation detail during the date range, I want to know the value of those open nights by finding them on the rate table.
Example: Date Range for 8/4/2018 -8/7/2018 (4 nights)
Property- Gray Home has 0 'Reservation' [ID] or 0 'Reservation Detail' [night of stay] found for those dates
So by using the property id, reference the rate table and the date range to return the "listed" rate/price for those dates.
Rate= $496
8/4/2018= $140
8/5/2018= $101
8/6/2018= $155
8/7/2018= $100
Property: Blue House - Has Reservation activity during this range. The detail table shows a reservation id for all 4 'nights of stay' in the date range.
This slicer would not show this property because the date range shows a value for the night of stay. Therefore I have no available inventory or nights to sell for the Blue House.
Property: Yellow Condo - has no activity or reservation id found for that date range.
So for that date range, I have 4 nights I can sell for the Yellow Condo. And the rate table shows that those nights are worth $1239.
8/4/2018= $825
8/5/2018= $138
8/6/2018= $138
8/7/2018= $138
So my visual would show
Slicer: Date- 8/4/2018 -8/7/2018
Blue House = 0 (or the slicer may not show this property)
Yellow Condo = $1239
Gray Home = $496
Total Opportunity Remaining= $1735
Sorry for typing so much. I hope this helps. To sum it up, if we look at it like inventory. Every Property has a Calendar Day for sale. If that date is not sold or found in the reservation table, I want to see the sum value for those dates for each property.
oh sorry... This is the reservation table
Reservations | |||||||||
ID | Property ID | Agent | Check-in | Check-out | # Nights | Rent | Booking date | Status | Type |
204 | 14 | Carl | 8/4/2018 | 8/11/2018 | 7 | 2100 | 3/15/2018 | Confirmed | Gues |
205 | 15 | Thor | 8/8/2018 | 8/13/2018 | 5 | 3300 | 3/1/2018 | Confirmed | Owner |
208 | 13 | Carl | 6/5/2018 | 6/8/2018 | 3 | $0 | 2/1/2018 | Checked Out | Block |
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |