Calculating the total occupancy and RevPAR for hotels

[Lamia]

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

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

[yelsherif · Accepted solution]

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

[yelsherif]

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]

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

[yelsherif]

Sorry, I made a mistake, check this one

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

[yelsherif · Accepted solution]

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

[Lamia]

Wow, his is working just fine!!

Thank you very very much !!!

[ninakarsa]

Hi Lamia I wounder if you could help me regarding your solution on occupancy and Rev for hotels. I have data with the booking information for the car park and I want to be able to calculate how many spaces are occupied for any given day. For example, the table below shows the arriving date (adate) and departing date (ddate) of customers entering the car park. If i was to calculate how many car park spaces are occupied on the 2/05/2019 this will be 8

Customer ID adate ddate

123      01/05/2019  05/05/2019

127 01/05/2019 03/05/2019

130 01/05/2019 03/05/2019

133 01/05/2019 03/05/2019

124 02/05/2019 04/05/2019

128 02/05/2019 04/05/2019

131 02/05/2019 04/05/2019

134 02/05/2019 04/05/2019

125 03/05/2019 05/05/2019

[ninakarsa]

@Lamia 

Hi Lamia

I wounder if you could help me regarding your solution on occupancy and Rev for hotels. I have data with the booking information for the car park and I want to be able to calculate how many spaces are occupied for any given day.

For example, the table below shows the arriving date (adate) and departing date (ddate) of customers entering the car park. If i was to calculate how many car park spaces are occupied on the 2/05/2019 this will be 8

Customer ID	adate	ddate
123	01/05/2019	05/05/2019
127	01/05/2019	03/05/2019
130	01/05/2019	03/05/2019
133	01/05/2019	03/05/2019
124	02/05/2019	04/05/2019
128	02/05/2019	04/05/2019
131	02/05/2019	04/05/2019
134	02/05/2019	04/05/2019
125	03/05/2019	05/05/2019

[Lamia]

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

[yelsherif · Accepted solution]

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