Calculate Potential Revenue from Occupancy

[Anonymous]

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
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