How to calculate the occupancy of the hotel by room category from the certain chosen year and month

[XYZHotel]

 My hotel has 8 different room types and each room type has its own total inventory which is.

Room A has a total of 20 units per day inventory.

Room b has a total of 33 units per day inventory.

Room c has a total of 62 units per day inventory.

Room d has a total of 84 units per day inventory.

Room e has total of 21 units per day inventory.

Room f has a total of 16 units per day inventory.

Room g has a total of 3 units per day inventory.

Room h has a total of 1 unit per day inventory’.

Total Inventory of the hotel per day is 180 in together.

Lets assume that the total sold room for January 2023 is 3710 where the room nights were shared from the below room types as ,

Room a is sold with total of 468

Room b is sold with total of 568

Room c is sold with total of 768

Room d is sold with total of 568

Room e is sold with total of 968

Room f is sold with total of 168

Room g is sold with total of 184

Room h is sold with total of 18

Lets assume that the total sold room for February 2023 is 3410 where the room nights were shared from the below room types as ,

Room a is sold with total of 168

Room b is sold with total of 568

Room c is sold with total of 768

Room d is sold with total of 568

Room e is sold with total of 968

Room f is sold with total of 168

Room g is sold with total of 184

Room h is sold with total of 18

Now When I filter to get the result, I would bring year, month and room type as row column and total nights booked in the column1. Now in column two I want to get the occupancy report for the booked room nights from the total available to sell which is 180 per day. Same time there is a criteria that when you see the total as row by year it would show you by year with the expanded month  , it would give you the total room nights occupied by the room night to be sold without any filter ( this is most obvious result everyone could get ) for that particular month ( based on the total room inventory available ). Now when the month is expanded by the room type, the occupancy should be calculated by the breakdown of the room nights occupied by the room types with their inventory ; which is if room A has a total unit of 20 units per day, for a month ( assuming January)  it would have 620 units , now when the month for January is expanded to view the room type production , then the occupancy per room type should be given or shown as per their inventory and not the total unit which is 180.

for January , Room A was sold with 468 , the occupancy should be calculated as 468/620*100 = 75.48 and same way to other room types.

Can you please helm me here.

[v-jingzhang · Accepted solution]

Hi @XYZHotel 

You can add a Date table with below DAX and build relationships between tables like the image. 

Date = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",FORMAT([Date],"mmm"),"MonthNumber",MONTH([Date]),"YearMonth",YEAR([Date])*100+MONTH([Date]))

Then create measures:

Booked RMs = SUM(TestingDatasheet[Rooms Booked])

Total RMs = SUM(RoomDetails[Number of Rooms in Category])

Occ% = DIVIDE([Booked RMs], COUNT('Date'[Date]) * [Total RMs])

I have attached a sample file at bottom for your reference. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[XYZHotel]

@FreemanZ 
the excel file is in this link https://filetransfer.io/data-package/hyn7sBiY#link 
Below the table SC, 

Here BBG is A and so on respectively

I wanted to  have the occupancy based on the year and month selected from the total available inventory ( you may see the room numbers available unit in the excel ) , and while it shows the total in year - it should have it automatic calculated with number of days in the year which is 365 * number of units to be sold for the room night booked.

In the row total of month, it should calculated by no of days in the month for that particular year choosen 
and in the room typoes, it whould calculate, number of room booked / total unit room avaiilable for the month ( 180*31) and so on for other rooms types as well.

[v-jingzhang · Accepted solution]

Hi @XYZHotel 

You can add a Date table with below DAX and build relationships between tables like the image. 

Date = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",FORMAT([Date],"mmm"),"MonthNumber",MONTH([Date]),"YearMonth",YEAR([Date])*100+MONTH([Date]))

Then create measures:

Booked RMs = SUM(TestingDatasheet[Rooms Booked])

Total RMs = SUM(RoomDetails[Number of Rooms in Category])

Occ% = DIVIDE([Booked RMs], COUNT('Date'[Date]) * [Total RMs])

I have attached a sample file at bottom for your reference. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[XYZHotel]

@v-jingzhang , this is really helpful , also if you could help me in getting a same time last year details. Here same time last year is not the total rooms that i had back in samedate last year based on on the stay date, but i need to have it based on the booking date. For Example , if today is 12/04/2023 - same time last year based on the stay period will be total rooms till 12/04/2023 , i would need that as well but also would need to know what was my total room nights based on the booking date which is from the start of the business till 12/04/2022 for any given /selected period of date ? and this should show in in the same column of theyear such as when you are looking at 2023, jan , the above asked question should be shown together - i tried to do this but it gives me some odd numbers when it breakdowns in to room types ;

pls help me here

[v-jingzhang]

Hi @XYZHotel 

Sorry I don't understand the new requirement well. Can you give a more detailed example that how a result should be calculated for a room type/month/year? And the current matrix only has the granularity to month, so for any selected date how should it be calculated? Or perhaps you will always calculate the result based on the moving "today"?

In an example, can you show how to calculate the result for BBG in January 2023 when today is 13/04/2023? And what is the specific result based on the current sample data?

[XYZHotel]

@v-jingzhang - sorry for being not clear .

as in the sheet you have two seperate column with one Booked Date and one Stay Date. In your example , you have a measure where i could see total rooms for this year and with room type . now i want another result where in the same line which should same time last year overall with the room types room booked , where the function Sameperiodlastyear can be used . This result wiull give you based on the stay date ( since stay date is the main date to be measured). This is also okay. ( y ou can see the result in my earlier provided screenshot as in column "LY-RM"
I also want a third coulum which would measure the rooms that was booked by same period last year ( based on the booked date) resulting for the same month and date last year by stay date.
Exaample from 2017-01-01 till today date the you will see total of 170210 rooms booked 

Please note that this data is filtered by stay date.  

Now if i want to have a result what was booked last year same time but filtered by booked date and not stay date . from 2017-01-01 till 2023-04-13 should be the booked date filter and stay date should be from 2017-01-01 till date . When in result it should show as 

in this screenshot ,
-the first column in for this year what we had,
-second column what we had last year
- and third coulmn what we have same time last year filterd on the booking date 

[v-jingzhang]

Hi @XYZHotel 

Please check the new pbix file attached at bottom. Notice that I add an inactive relationship between 'Date'[Date] and 'TestingDatasheet'[Booked Date] and use USERELATIONSHIP function in two measures. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[XYZHotel]

@v-jingzhang 

thank you for your dedication in solving the asked task. This year details and last year details are all matching, however same time last year is not . For same time last year - if you filter manually in the excel , where you filter the booked date from the start till 13/04/2022, for the stay period of ( lets say) April 2022 , it should be 3713 - the same has to felect in the report where i am able to see that the report shows 2687.

will you again help me here please .

[v-jingzhang · Accepted solution]

Hi @XYZHotel , here is the sample pbix file. 

[XYZHotel]

@FreemanZ  , checking weather you are able to help here ?

[FreemanZ]

hi @XYZHotel 

it would be easier if you could depict your case with data tables.