Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX to Calculate Rate based on Occupancy/Availability

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,

 

 

 

Data.png

2 REPLIES 2
v-danhe-msft
Employee
Employee

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: DAX to Calculate Rate based on Occupancy/Availability

 [ Edited ] 
 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.