Hi - I’m trying to calculate daily utilization (occupancy) for a group of hotels; but cannot find a way to do so with Power Query or PowerPivot.
They have multiple sites across the city of Amsterdam (and few other cities), with differing room types. Their data consists of arrival date/time (check in) and departure date/time (check out), per visitor (room).
I’m fine aggregating number of arrivals and departures by date/time and then able to do analysis on time of day activity by weekday, seasonality, etc. But I’d really like to add the number of rooms utilized as well and analyse by room type, time of day/weekday/season, hotel, etc.
Any thoughts on how to do this when starting off with couple of 100k records of individual visitor check in and check out records; each staying between couple of hours (no questions asked …) to couple of days/weeks? I was thinking perhaps create a record for each visit for each day, with either a full day count or a part day count; it would multiply my number of records by about 2 (no issue), but make it quite easy to aggregate. Can this be done with Power Query (couldn't find it myself in Data Monkey (great book by the way!)) or should I look to create a formula with Power Pivot (haven’t managed to do here yet either)?
Any thoughts?! I have attached an Excel example of couple of records.
Many thanks!
They have multiple sites across the city of Amsterdam (and few other cities), with differing room types. Their data consists of arrival date/time (check in) and departure date/time (check out), per visitor (room).
I’m fine aggregating number of arrivals and departures by date/time and then able to do analysis on time of day activity by weekday, seasonality, etc. But I’d really like to add the number of rooms utilized as well and analyse by room type, time of day/weekday/season, hotel, etc.
Any thoughts on how to do this when starting off with couple of 100k records of individual visitor check in and check out records; each staying between couple of hours (no questions asked …) to couple of days/weeks? I was thinking perhaps create a record for each visit for each day, with either a full day count or a part day count; it would multiply my number of records by about 2 (no issue), but make it quite easy to aggregate. Can this be done with Power Query (couldn't find it myself in Data Monkey (great book by the way!)) or should I look to create a formula with Power Pivot (haven’t managed to do here yet either)?
Any thoughts?! I have attached an Excel example of couple of records.
Many thanks!