Hotel occupancy rates

[GilesWalker]

Hi everyone,

I have been stuck on this issue for a few days ad am probably over thinking things, so I am hoping someone out there can provide a tidy solution.

I have created some dummy data for a hotel and need to work out the occupancy rates (total rooms / filled rooms). The total rooms is 48, here is the data:

Room_ID	Guest_ID	Date_from	Date_to
109	74	1/01/2016	6/01/2016
217	85	1/01/2016	5/01/2016
210	16	1/01/2016	6/01/2016
116	53	1/01/2016	5/01/2016
209	129	1/01/2016	3/01/2016
214	82	2/01/2016	3/01/2016
215	96	2/01/2016	3/01/2016
204	13	2/01/2016	7/01/2016
108	18	3/01/2016	6/01/2016
115	101	5/01/2016	8/01/2016
207	12	5/01/2016	7/01/2016
104	23	6/01/2016	7/01/2016
403	138	7/01/2016	12/01/2016
111	15	7/01/2016	11/01/2016
404	131	7/01/2016	12/01/2016
106	95	7/01/2016	12/01/2016
304	95	8/01/2016	9/01/2016
302	17	8/01/2016	13/01/2016
212	109	9/01/2016	12/01/2016
302	4	9/01/2016	14/01/2016

Its a fairly straight forward table. However my issue is if I want to work out the occupancy rate on a day, week, month, or year how to get a dax formula to calculate the days inbetween the date_from and date_to. I have thought it could be done with total number of days in the relevant time frame, however would be interested to see the communities ideas.

The report has a date key with a continuous dates and all the relevant weeks, months, etc. This is linked via the Date_from column.

Thanks,

Giles

[edinvz]

Not sure if the problem is too complex or I confused you.

If anyone is willing to help I will provide more details.

Edin

[edinvz]

Thanks for your replies @GilesWalker and @Ashish_Mathur, they are highly appreciated.

I have attemted to use provided formula to create a measure, but it throws the error I have experienced with other attempts to create this measure: 

'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.'

I might be applying it in a wrong way, but it also seems like the question of how my data table looks like as pointed by @Ashish_Mathur can be of use.

My initial data table contains rows for each reservation per (berth) room, customer, product, start date and end date.

Id	Berth__c	End_Day__c	Start_Day__c	Products__c	AccountID
a4Jb0	a4Kb0	16-06-16	15-06-16	a7jb0
a4Jb0	a4Kb0	23-09-17	22-09-17	a7jb0
a4Jb0	a4Kb0	23-09-17	22-09-17	a7jb0
a4Jb0	a4Kb0	24-09-17	23-09-17	a7jb0
a4Jb0	a4Kb0	20-06-16	15-06-16	a7jb0
a4Jb0	a4Kb0	15-08-17	13-08-17	a7jb0
a4Jb0	a4Kb0	03-08-17	02-08-17	a7jb0
a4Jb0	a4Kb0	16-06-16	15-06-16	a7jb0

Obviously all of the values appear many times, as the dataset contains few years of reservations.

Edit: what I am trying to get is the occupancy by month and length for instance, the example of occupancy length for one period is presented below. Columns Count and Area as well as length of berths are added into Data table from related tables. The primary question is how to find the Occ count based on Data table. 

Length	Count	Area	Occ Count	Occ Area	Occ% Count	Occ% Area
6	8	144	6.40	115.20	80%	80%
7	10	210	8.00	168.00	80%	80%
12	30	1,440	24.00	1,152.00	80%	80%
13.5	20	1,080	16.00	864.00	80%	80%
15	30	2,250	24.00	1,800.00	80%	80%
17	20	1,700	16.00	1,360.00	80%	80%
18	10	1,080	8.00	864.00	80%	80%

Thanks in advance and let me know if I can help you with any additional information

Edin 

[Ashish_Mathur]

Hi,

I do not understand how you have computed the numbers in the Occ Count column.  Please explain in detail.

[edinvz]

My apologies for not being clear enough. 

That information comes from a different table. But as I said the focus is on Occupied Berths Count and that's the only bit of information to be calculated based on the presented data table.

I hope it makes more sense now.

Thansk a lot!

Edin

[Ashish_Mathur]

I still do not understand.  How have you computed the numbers in the Occ Count column?  How did you arrive at the numbers - 6.4,8,24 etc? 

[edinvz]

That's exactly what I want to get:, the second table is the output I am afterL my question is how to create measure that would calculate Occupancy Count based on the first table (Data table that contains reservations and all related features).

[edinvz]

Dear Power BI pros 🙂

I have tried to crack this one for some time and still cannot manage to get what I need.

I want to get the measure that would calculate the number of occupied rooms each day, but also getting the average number of rooms occupied when aggregated to monthly and annual basis.

I have solved it by creating the Calculated Column using the measure [Rooms Occupied] provided by rdurkin and then averaged it. It works just fine for total occupancy, but wait does not allow for slicing and dicing by different segments e.g. product, room type, size and similar. 

I would appreciate if anyone could give a try to suggest a solution for the problem outlined above.

And just in case it makes difference, I am working in Excel Power Pivot, rather than Power BI.

Thanks in advance and looking forward to see how genius Dax measures can be.

Edin

[Ashish_Mathur]

Hi @edinvz,

Share the link from where i can download your Excel workbook and also the expected result there.

[GilesWalker]

@edinvz - I managed to find a solution to this and have used it a few times and it does work well:

Daily Table =
FILTER (
CROSSJOIN ( Table1, CALENDARAUTO () ),
Table1[date_from] <= [Date]
&& [Date]
<=
VAR EndDate = Table1[date_to]
RETURN
IF ( EndDate = BLANK (), TODAY (), EndDate )
)

[Anonymous · Accepted solution]

Think I've got it solved for you:

Add an index column to your Bookings table, since I'm assuming the GuestID refers to a specific person and that person can book multiple rooms at a time or across a given time period.

Add a Date Dimension.

Your model should look like the following:

Create the following measures:

Rooms Occupied:=CALCULATE(DISTINCTCOUNT(Bookings[Index Column]),Filter(Bookings,[Date_from]<=LASTDATE(DimDate[Date])&&[Date_to]>=FIRSTDATE(DimDate[Date])))

Rooms Available (Total for Selected Dates):=CALCULATE(DISTINCTCOUNT(DimDate[Date]))*48

Total Dates Booked:=CALCULATE(DISTINCTCOUNT(DimDate[Date]),Bookings)

Total Rooms Occupied:=SUMX(Bookings,[Rooms Occupied]*[Total Dates Booked])

OccupancyRate:=DIVIDE([Total Rooms Occupied],[Rooms Available (Total for Selected Dates)])

Or

OccupancyRate:=DIVIDE([Total Rooms Occupied],[Rooms Available (Total for Selected Dates)])*DIVIDE([Rows in Bookings],[Rows in Bookings])

Pivot Table Examples:

Please mark it as a solution or give a kudo if it works for you, otherwise let me know if you run into an issue and I'll do my best to assist. Go To bipatterns.com for more techniques and user guides.

Thanks,

Ryan Durkin

[Yggdrasill]

Any idea how to fill in the blanks ?
E.g. there should be 5 on 7th and 9 through out September...

[Ashish_Mathur]

Hi,

Explain the question and share the data (in a format that can be pasted in an Excel file)

[Yggdrasill]

I'm using @Anonymous proposed solution and I get blanks where there is no-one checking in.

Everything else is correct, I just need the numbers to be cumulative

[GilesWalker]

@Anonymous - thanks for you help with this your solution worked as it takes into account the total available days for rooms and then uses the actual count of time a room is booked.

@sdjensen - thanks for your assistance with this, you were spot on in that you have to take into account the days available compared to days booked out per room.

@KHorseman - thanks for all the help with this and for clarifying what the measures were doing. 

[GilesWalker]

@KHorseman - Having some trouble this query again, turned out the numbers were not calculating correctly and part of the formula you gave worked with a bit of tweaking to give me the total days booked. To calculate the total days booked I am using this:

Inclusive days booked = SUMX(

FILTER(Bookings, Bookings[Date_from] <= LASTDATE (DateKey[Date]) && Bookings[Date_to] >= FIRSTDATE (DateKey[Date])),

COUNTROWS(DATESBETWEEN (DateKey[Date],Bookings[Date_from],Bookings[Date_to])))

The issue I run into is with the reporting over time periods. If I have a room booked out from 30 January to 4 February that means that in January there were two days booked, and in February there were four days booked. However the above formula says booked for six days which is correct however it allocates those six days to the month i have the datekey relationship linked to (i.e if the date_from is in January then January gets the six days). I have tried changing the DateKey table relationship to be linked to either Date_from and Date_to in the bookings table, it makes no difference.

Is there a filter that can be used so that when filtering by a month/week/quarter etc. a measure will calculate only the days in the month. In this example January would have two days allocated (30 and 31 January), and February would get four days (1-4 February).

Heres a picture of what I am currently dealing with:

Thanks,

Giles

[KHorseman]

@GilesWalker For any of the solutions I've suggested there should be no relationship at all between Bookings and DateKey. Any relationship will break these durational measure patterns. From there you should be able to use this:

Inclusive Days Booked = SUMX(
	FILTER(
		Bookings,
		Bookings[Date_from] <= LASTDATE(DateKey[Date]) &&
		Bookings[Date_to] >= FIRSTDATE(DateKey[Date])
	),
	CALCULATE(
		DISTINCTCOUNT(DateKey[Date]),
		DATESBETWEEN(
			DateKey[Date],
			Bookings[Date_from],
			Bookings[Date_to]
		)
	)
)

This only works sensibly if you only have one room selected at a time, or if you do something like a clustered bar chart with room_ID as the legend. Otherwise the same day will be counted multiple times, once for each room that is occupied during the time period. But your screenshot looks like it's only for one room at a time anyway.

I'm fairly certain that putting a CALCULATE inside a SUMX is some sort of DAX sin but I can live with that.

[Anonymous]

@GilesWalker

This is a pretty interesting request.  If you have a dummy excel file available, I'd be happy to provide a solution.  Attach it here if possible.  In order to help I'll need a better understanding of the data model in this case.

[GilesWalker]

@Anonymous happy to, how do I do that?

[KHorseman]

@GilesWalker So that you don't have to wade through that other thread to pick the best of the offered solutions, I'd recommend the following:

First, that relationship between Date_from and the date table. There should be no table relationships for this to work. Again, see that other thread for the details. Then here's your occupancy measure:

Occupancy = CALCULATE(
DISTINCTCOUNT(TableName[Room_ID]),
FILTER(
TableName,
TableName[Date_from] <= LASTDATE(DateTable[Date]) &&
TableName[Date_to] >= FIRSTDATE(DateTable[Date])
)
)

You can place any time period from the date table next to this measure and it will work. A month, a week, a single day... I have it counting each room only once, but you may want to do a distinctcount of guests instead if you want to count the same room multiple times if it is occupied multiple times in a period. Or you could do a regular count of rooms instead of a distinctcount. Depends on your specific needs.

[GilesWalker]

Thanks @KHorseman I just finished reading through the information on you link and your idea seems nice and clean. Once I get into work in a couple hours I will try both yours and @Sean idea to see what works best.

I will reply back with what I have done.

Giles