Forum Discussion
Condo/ Property Availability
I'm apologizing in advance as I'm well aware that this topic has been covered many times before. Though in all honesty, not exactly in the way that I am needing. It seems that when figuring Occupancy, depending on the user this can differ greatly. So, rather than look for the Occupancy percentage, I would like to find the availability. Also, not focusing so much on what is available for each day but what property is available for a select number of days. For instance, looking at my reservation report I would like to know how many and which properties are open starting on 5/3/2018 for a 4-night stay or 6/2/2018 for 10 nights.
Every reservation has a unique Reservation ID through a Unit ID can be on multiple reservations. This report has a relationship to the Unit List established through the Unit ID. I've included a dummy version of my reservation report.
I've included a dummy version of my reservation report. I also have a Unit List that has a unique unit ID as well as relevant information for each property. No duplicates. However, some units are active and some are no longer active in our system and this shows in a "status" column.
My problem when trying to use some of the other solutions varies. Mainly, due to the fact that I may try to compare available rooms to This Day Last Year, or I want what open from May 3, 2018, for 3 nights and not May 3, 2016, 2017, etc... to Sum it up, If someone were to call looking for a property to rent, I want to be able to see a distinct count and list of property names of what I have available for any given date range or "between dates" slicer.
I truly appreciate any assistance. I've decided to start this over from scratch in hopes of figuring this out. Thanks
| Reservation Report | |||||||||||||
| Reservation ID | Property | Rent | Check In | Check out | Booking Date | Area | Tax | Fee | Total | Status | Source | Room Id | Customer Name |
| 1 | Blue House | $500.00 | 1/2/2018 | 1/9/2018 | 11/2/2017 | City A | $335.00 | $95.00 | $930.00 | Checked Out | Online | 1 | |
| 2 | Red House | $2,500.00 | 5/15/2018 | 5/22/2018 | 1/9/2018 | City b | $421.00 | $60.00 | $2,981.00 | Checked In | Agent | 2 | |
| 3 | Green House | $300.00 | 5/17/2018 | 5/25/2018 | 4/5/2018 | City C | $111.00 | $32.00 | $443.00 | Checked In | Channel | 3 | |
| 4 | Yellow Condo | $2,300.00 | 4/14/2018 | 4/21/2018 | 1/2/2018 | City A | $156.00 | $44.00 | $2,500.00 | Checked Out | Online | 4 | |
| 5 | Black house | $400.00 | 5/1/2016 | 7/5/2016 | 4/5/2018 | City b | $219.00 | $78.00 | $697.00 | Cancelled | Agent | 5 | |
| 6 | Purple condo | $2,400.00 | 7/11/2018 | 7/18/2019 | 5/15/2018 | City C | $355.00 | $109.00 | $2,864.00 | Confimed | Channel | 6 | |
| 7 | Blue House | $1,920.00 | 6/2/2018 | 6/9/2018 | 5/15/2018 | City A | $421.00 | $222.00 | $2,563.00 | Confirmed | Online | 1 | |
| 8 | Red House | $2,068.57 | 9/5/2018 | 9/10/2018 | 5/15/2018 | City b | $269.00 | $42.00 | $2,379.57 | Confirmed | Agent | 2 | |
| 9 | Green House | $2,217.14 | 2/2/2018 | 2/13/2018 | 1/2/2018 | City C | $581.00 | $111.00 | $2,909.14 | Checked Out | Channel | 3 | |
| 10 | Gray Room | $2,365.71 | 4/5/2018 | 4/15/2018 | 1/2/2018 | City A | $242.00 | $67.00 | $2,674.71 | Checked Out | Online | 7 | |
| 11 | Green House | $2,514.29 | 7/1/2018 | 7/12/2018 | 4/5/2018 | City b | $301.00 | $63.00 | $2,878.29 | Hold | Agent | 3 |
| Unit List | ||||
| ID | Name | Status | # Rooms | Type |
| 1 | Blue House | Active | 3 | House |
| 2 | Red House | Active | 4 | Condo |
| 3 | Green House | Active | 5 | House |
| 4 | Yellow Condo | Active | 2 | Condo |
| 5 | Black house | Active | 3 | Condo |
| 6 | Purple condo | Active | 4 | Condo |
| 7 | Gray Room | inactive | 5 | House |
HI Anonymous,
You can refer to below steps if it suitable for your requirement.
Steps:
1. Create a expand table with detail date from date range of each record.Expand Table = VAR _calendar = CALENDAR ( FIRSTDATE( Reservation[Check In] ), LASTDATE( Reservation[Check out] ) ) RETURN SELECTCOLUMNS ( FILTER ( CROSSJOIN ( Reservation, _calendar ), [Date] >= [Check In] && [Date] <= [Check out] ), "Reservation ID", Reservation[Reservation ID], "Detail Date", [Date] )2. Link original table by 'Reservation ID' column.
3. Write measure to check dynamic status of selection date range.
Available = IF ( LASTDATE ( ALLSELECTED ( 'Expand Table'[Detail Date] ) ) IN CALCULATETABLE ( VALUES ( 'Expand Table'[Detail Date] ), ALLSELECTED ( Reservation[Room Id] ) ), "N", "Y" )4. Create visuals.
Notice: I attach pbix file below.
Regards,
Xiaoxin Sheng
4 Replies
HI Anonymous,
You can refer to below steps if it suitable for your requirement.
Steps:
1. Create a expand table with detail date from date range of each record.Expand Table = VAR _calendar = CALENDAR ( FIRSTDATE( Reservation[Check In] ), LASTDATE( Reservation[Check out] ) ) RETURN SELECTCOLUMNS ( FILTER ( CROSSJOIN ( Reservation, _calendar ), [Date] >= [Check In] && [Date] <= [Check out] ), "Reservation ID", Reservation[Reservation ID], "Detail Date", [Date] )2. Link original table by 'Reservation ID' column.
3. Write measure to check dynamic status of selection date range.
Available = IF ( LASTDATE ( ALLSELECTED ( 'Expand Table'[Detail Date] ) ) IN CALCULATETABLE ( VALUES ( 'Expand Table'[Detail Date] ), ALLSELECTED ( Reservation[Room Id] ) ), "N", "Y" )4. Create visuals.
Notice: I attach pbix file below.
Regards,
Xiaoxin Sheng
Anonymous Thank you again. This appears as though it will work for my needs. I do have a question to pose. As my "dummy" reports are very small, creating a "detail date" for each day of a reservation works smoothly. However, my actual report contains nearly 40,000 reservations over the course of 4 years and averages almost 120 new (avg. 6 nights per reservation) reservations daily. Creating a "detail date" for that many reservations may seem very daunting of a task. As we speak I've been waiting 20 minutes with a status of "Working on it" for the Expand Table to complete.
Do you think given the magnitude of my data this is still the best approach? Thanks.