cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Lamia Frequent Visitor
Frequent Visitor

Calculating the total occupancy and RevPAR for hotels

Hello,

 

Would you please help me on the below

I am not able to have the total occupancy and RevPAR calculated correctly

Here is the case:

There are 2 Hotels (Properties), with different number of rooms

Prop1 = 300 rooms and Prop2 = 170 rooms

  • The occupancy would be the number of total rooms sold (RoomNight) in a given month divided by the capacity of the given property multiple number of days in a month

 

Occ% = DIVIDE(SUMX(Revenue,Revenue[RoomNight]),[All Rooms])

 

I used the below measure to calculate the availble rooms per month but this is not given me the correct total per year of even when I select more than one month

All Rooms = SUMX(Inventory,Inventory[Inventory])*SUMX(DISTINCT('Calendar'[DaysInMonth]),'Calendar'[DaysInMonth])

 

  • RevPAR is the Revenue per available room. For a given month it would be the total revenue of that month divided per the inventory multiply per number of days of that month

 

Pic1.png

 

Pic2.jpg

For all this calculation, I can filter by property to have the result either per property or both properties

 

My issue is that the total per year is not given the correct number. The same issue if I select more than 1 month and this is because my measure “All Rooms” is not given the right total of the months selected

 

I am sharing the link to the .pbix file if needed

https://filedn.com/lRKl0pgmdmYugsFfu7PUhjL/Test%20Occ%20and%20RevPAR.pbix

 

Thanks you in advance for your help

2 ACCEPTED SOLUTIONS

Accepted Solutions
yelsherif Member
Member

Re: Calculating the total occupancy and RevPAR for hotels

You then need to change the cross filter direction between Calendar and Revenue tables to be "both" so both tables filter each other. This should make correct calculations for months and total
Capturexx.PNG

yelsherif Member
Member

Re: Calculating the total occupancy and RevPAR for hotels

All Rooms = SUMX(Inventory,Inventory[Inventory])*SUMX('Calendar','Calendar'[DaysInMonth])
7 REPLIES 7
yelsherif Member
Member

Re: Calculating the total occupancy and RevPAR for hotels

You just need to remove the DISTINCT part as follows

 

All Rooms = SUMX(Inventory,Inventory[Inventory])*SUMX('Calendar'[DaysInMonth],'Calendar'[DaysInMonth])

Because DISTINCT will add the unique possible values of days in month, which are 28, 30, 31 = 89 then multiplies the total by the number of rooms in the inventory: 300 x 89 = 26700

Lamia Frequent Visitor
Frequent Visitor

Re: Calculating the total occupancy and RevPAR for hotels

I tried to remove Disctinct but now the measure is given me an error - please see below

 

Pic3.jpg

 

yelsherif Member
Member

Re: Calculating the total occupancy and RevPAR for hotels

Sorry, I made a mistake, check this one

All Rooms = SUMX(Inventory,Inventory[Inventory])*SUMX(Calendar,'Calendar'[DaysInMonth])

 

 

Lamia Frequent Visitor
Frequent Visitor

Re: Calculating the total occupancy and RevPAR for hotels

Now this measure is not giving the correct total per month which should be for examble 300 (capacity) per number of days 31 (for Jan) = 9300 and 8400 for Feb... etc

Pic4.jpg

 

yelsherif Member
Member

Re: Calculating the total occupancy and RevPAR for hotels

You then need to change the cross filter direction between Calendar and Revenue tables to be "both" so both tables filter each other. This should make correct calculations for months and total
Capturexx.PNG

yelsherif Member
Member

Re: Calculating the total occupancy and RevPAR for hotels

All Rooms = SUMX(Inventory,Inventory[Inventory])*SUMX('Calendar','Calendar'[DaysInMonth])
Lamia Frequent Visitor
Frequent Visitor

Re: Calculating the total occupancy and RevPAR for hotels

Wow, his is working just fine!!

Thank you very very much !!!