cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mikejay850 Regular Visitor
Regular Visitor

Calculate Potential Revenue from Occupancy

Hello all. Its always the questions regarding occupancy that bring me back here. Previously with the help of @v-shex-msft , 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     
IDProperty IDAgentCheck-inCheck-out# NightsRentBooking dateStatusType
20414Carl8/4/20188/11/2018721003/15/2018ConfirmedGues
20515Thor8/8/20188/13/2018533003/1/2018ConfirmedOwner
20813Carl6/5/20186/8/20183$02/1/2018Checked OutBlock
          
          
          
          
          
 Properties       
IDNameLocation# BedsAbbrev.     
14Blue HouseCity A3BlueH     
15Yellow CondoCity A5YellowC     
13Gray HomeCity C2GrayH     
          
          
          
          
          
          
Rate IDSeason IDRateDowDateProperty IDSeason StartSeason EndCalendar Group 
122282518/4/2018148/4/20188/10/201814 
222282528/5/2018148/4/20188/10/201814 
322282538/6/2018148/4/20188/10/201814 
422282548/7/2018148/4/20188/10/201814 
522282558/8/2018148/4/20188/10/201814 
622282568/9/2018148/4/20188/10/201814 
722282578/10/2018148/4/20188/10/201814 
822282518/4/2018158/4/20188/10/201814 
922213828/5/2018158/4/20188/10/201814 
1022213838/6/2018158/4/20188/10/201814 
1122213848/7/2018158/4/20188/10/201814 
1222213858/8/2018158/4/20188/10/201814 
1322213768/9/2018158/4/20188/10/201814 
1422213978/10/2018158/4/20188/10/201814 
1522214018/4/2018138/4/20188/10/201814 
1622210128/5/2018138/4/20188/10/201814 
1722215538/6/2018138/4/20188/10/201814 
1822210048/7/2018138/4/20188/10/201814 
1922210558/8/2018138/4/20188/10/201814 
2022212968/9/2018138/4/20188/10/201814 
2122213378/10/2018138/4/20188/10/201814 
          
          
Calendar         
The Date  Reservation Detail     
8/4/2018  Reservation IDNight of Stay     
8/5/2018  2086/8/2018     
8/6/2018  2086/9/2018     
8/7/2018  2086/10/2018     
8/8/2018  2058/8/2018     
8/9/2018  2058/9/2018     
8/10/2018  2058/10/2018     
   2058/11/2018     
   2058/12/2018     
   2048/4/2018     
   2048/5/2018     
   2048/6/2018     
   2048/7/2018     
   2048/8/2018     
   2048/9/2018     
   2048/10/2018