Calculate Potential Revenue from Occupancy
Hello all. Its always the questions regarding occupancy that bring me back here. Previously with the help of Anonymous , I am able to take data from my reservations and create a "daily detail" for each day of a reservation. Essentially by seeing every night of a booking, I can then use a slicer to see the nights that a room/property has no detail and is, therefore "available". This is helpful when looking at what I have open for any given date range. I am having trouble finding a way to reference this back to a rate table and sum the potential revenue for the selected date(s) for each available property. In my data set, within the rate table, every property has a rate for every day of the year and these dates are grouped by multiple seasons or calendar groupings. So essentially 1/1/2018 can exist for 1,000 properties and in multiple calendar sets. Each calendar group, however, has a unique code making this easy to map. So let's say that calendar group 14 is the one in question. Every property has a rate for every calendar day in this group. 1/1/2018 -12/13/2018 etc.... If I use the dates found in my reservation details as the filter or the Property found attached to those reservations, I lose my mapping back to the property in the rate table when a property is open or does not have a detailed date/reservation. I've created a condensed mini version (picture) of my dataset. Any advice on how to sum the potential rate for a property that is not booked for a given date range? Should I be looking at his from property level and then using the date as a filter, sum the rate table rates if # of bookings found equals 0? Or possibly starting with the date(s), sum the rates for each property if no reservation detail date is found? Any help is appreciated. Thanks, 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 Properties ID Name Location # Beds Abbrev. 14 Blue House City A 3 BlueH 15 Yellow Condo City A 5 YellowC 13 Gray Home City C 2 GrayH Rate ID Season ID Rate Dow Date Property ID Season Start Season End Calendar Group 1 222 825 1 8/4/2018 14 8/4/2018 8/10/2018 14 2 222 825 2 8/5/2018 14 8/4/2018 8/10/2018 14 3 222 825 3 8/6/2018 14 8/4/2018 8/10/2018 14 4 222 825 4 8/7/2018 14 8/4/2018 8/10/2018 14 5 222 825 5 8/8/2018 14 8/4/2018 8/10/2018 14 6 222 825 6 8/9/2018 14 8/4/2018 8/10/2018 14 7 222 825 7 8/10/2018 14 8/4/2018 8/10/2018 14 8 222 825 1 8/4/2018 15 8/4/2018 8/10/2018 14 9 222 138 2 8/5/2018 15 8/4/2018 8/10/2018 14 10 222 138 3 8/6/2018 15 8/4/2018 8/10/2018 14 11 222 138 4 8/7/2018 15 8/4/2018 8/10/2018 14 12 222 138 5 8/8/2018 15 8/4/2018 8/10/2018 14 13 222 137 6 8/9/2018 15 8/4/2018 8/10/2018 14 14 222 139 7 8/10/2018 15 8/4/2018 8/10/2018 14 15 222 140 1 8/4/2018 13 8/4/2018 8/10/2018 14 16 222 101 2 8/5/2018 13 8/4/2018 8/10/2018 14 17 222 155 3 8/6/2018 13 8/4/2018 8/10/2018 14 18 222 100 4 8/7/2018 13 8/4/2018 8/10/2018 14 19 222 105 5 8/8/2018 13 8/4/2018 8/10/2018 14 20 222 129 6 8/9/2018 13 8/4/2018 8/10/2018 14 21 222 133 7 8/10/2018 13 8/4/2018 8/10/2018 14 Calendar The Date Reservation Detail 8/4/2018 Reservation ID Night of Stay 8/5/2018 208 6/8/2018 8/6/2018 208 6/9/2018 8/7/2018 208 6/10/2018 8/8/2018 205 8/8/2018 8/9/2018 205 8/9/2018 8/10/2018 205 8/10/2018 205 8/11/2018 205 8/12/2018 204 8/4/2018 204 8/5/2018 204 8/6/2018 204 8/7/2018 204 8/8/2018 204 8/9/2018 204 8/10/2018
