Occupancy Per Day

[sgillmer]

Hi Community,

I am now trying to create useful measures like occupancy but am struggling to understand how I need to set this properly so that measure is correct in terms of date context.

In the attached data sets (xlsx) and powerbi report (prix) the folio / reservation is per row with check in check & out dates. I don’t now if I need to either a) transform the data first and/or b) use dax to create a measure to get an daily occ % (room nights sold / total nights occupancy). The issue seems to be the that the the room nights sold are per folio and not per day in the dataset.

I need some assistance please so that matrix visual columns accurately represent any type of date from the date table (day, month, year, quarter etc) for an occupancy %.

Thank you in advance.

Thanks,
Sean

Data_Dump_Oc.xlsx (2.2 MB)
Occupancy.pbix (4.6 MB)

[Kumail]

Hello @sgillmer

“the folio / reservation is per row with check in check & out dates.” which sheet does this data refer to?

And in the stock sheet, there are 6 tables with same column name? Why are they not in the tabular format when they have the same column header?

The above answers would help better understand your requirements.

Regards
Kumail Raza

[sgillmer]

Hello @Kumail,

I was only able to extract the folio / reservation per row on separate sheets because the client sent this for each of their sites e.g. Moria, KMIA, Kruger Gate, Machaka, Komatipoort and Lanseria.

If you look at the pbix file sent you will see I did some transformations and appended the tables into one. The stock sheet and tables were also appended into one table in the pbix file.

I trust this clarifies.

Thanks,
Sean

[deltaselect]

Good evening @sgillmer

Please receive hereby a workout to calculate the occupation rate.

The used calculation is actual bookings [Room nights] divided by available capacity in room-days. Capacity is selected by (multiple) periods and multiplied by (multiple) selected rooms and sites.

PBIX attached:
Occupancy v4.pbix (1.6 MB)

Work out explained:

1. Adjusted the data model, adding a Lookup table with Rooms per Site with an unique key to connect to the fact table “Site Occupation”

• Note 1 In my opinion, only three tables are needed to calculate the occupation, as presented below.

• Note 2 The new lookup table should NOT be a calculated table out of the Site-Occupation table, (as I did): if rooms of a site or a complete site are not booked, those will not be in the lookup table, and those capacity would be missing, better manually import this lookup table.

• Note 3: better NOT make Many to Many table relations, as was between Site_Inv and Site_Occ, unless you know exactly what it will be doing.

  The data model used for occupation rate calculation:

  

2. Set up the measures to calculate the occupation rate

Actual bookings

Used capacity (booked nights) = SUM(Site_Occupation[Room Nights])

Capacity calculation

Capacity in Room-Days = countrows(SUMMARIZE(DatesLT,DatesLT[Date])) --count the selected dates from the date table, count also for a combi of DayOfMonth and Year (eg 31 and 2021 gives a count of 7 only)
                                * COUNTROWS( 'LT Rooms per Site') --multiplied by the available unique rooms of selected sites 

Occupation rate:

Occupation % = DIVIDE( [Used capacity (booked nights)], [Capacity in Room-Days], 0)`

3. A possible report with two selections, looks as follows:

First: Limited selection on room code 5, DayOfMonth is 31, the year is 2021, and the Site is Krüger Gate, with a used capacity of 3 versus a capacity of 7 (from 7 days per year with month end day 31)

Second: selection on Site Krüger Gate, year 2021 with a Used capacity of 2.353 vs a capacity of 10.950 room days

=========
I hope this answers your question, please let me know if you have further questions or remarks.

Kind regards,
Jan van der Wind

[EnterpriseDNA]

Hello @sgillmer, good to see that you are having progress with your inquiry.

Did the response from @deltaselect help you solve your inquiry?

If it does, kindly mark as solution the answer that solved your query.

If not, how far did you get and what kind of help you need further?

[EnterpriseDNA]

Hello @sgillmer, it’s been a while since we got a response from you. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[Shamina]

Hi,

I am looking for something similar. I am however a novice!

I tried copying the same measures etc to use with my data. I have Rooms Available, available rooms per day and Booked nights.

I need to split the booked nights per month. E.g if a guest arrives in Oct 22 and departs in Nov 22 then the room nights need to be split in order to get an accurate occupancy per month.

Can anyone help me with this? I tried using the solution to this post and am not getting the correct booked Nights per month.

Thank you

[Keith]

Hi @Shamina
Can you please create a new topic as this topic is closed? you will get better results if you create a new topic instead of adding to an existing topic.

thanks
Keith

[Shamina]

Sure I will. Thank you.