Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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])
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
Solved! Go to 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
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
I tried to remove Disctinct but now the measure is given me an error - please see below
Sorry, I made a mistake, check this one
All Rooms = SUMX(Inventory,Inventory[Inventory])*SUMX(Calendar,'Calendar'[DaysInMonth])
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
Wow, his is working just fine!!
Thank you very very much !!!
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
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 |
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