Calculate occupancy rates for a business when dates with zero activity are not in the data table

1

Birthday Rooms occupancy.

I am running birthday rooms business. I have a database with reservations. It consists of such values as Birthday Date, Birthday Hour Slot (Whole number in 24-hour format from List from selected values; Ex 10, 13, 16, 19), Type of Birthday Room (List from selected values; Ex. Small, Big). Every reservation is listed in the new row and given a unique ID.

I also know the maximum availability of rooms and time slots for every day, so derivative values as such are calculated (they are explained in detail in the given Coda document)

What I want to do is to calculate and chart the average occupancy rate (in %):

  • for every day
  • for every birthday hour slot
  • for every week/month
  • for a given period

To do so I have to divide the real occupancy by maximum occupancy (max number of slots per day). That is not a problem to do with the days when there is occupancy. The calculations have been done in the Coda document attached. BUT there are some days and some hour slots on some days that are completely empty thus NOT reflected in the raw data. Therefore I miss calculating those days/hour slots and get averages that reflect only the days and hour slots that I have had birthday celebrations thus they show better occupancy than it reality it exists.

So my question is this: how can I “account for” the missing days, missing hour slots in such a case. Filling by hand it very difficult (I have over 1000 rows) so I am looking for an automated solution. The file is here:

If you could be so kind and share your ideas, I would truly appreciate that.

2

Okay, first what would really help both you and calculations is if you had each room uniquely identified. E.g., instead of just Big and Small, you had e.g. a table of all rooms that’d be numbered like Big 1, Big 2 etc. It was confusing for me at first to see two Big rooms booked on the same date at the same time.

Secondly, to calculate occupancy over time where entries are sparse, I’d just use absolute values:

  1. You can calculate overall slots between two dates with a formula. It would be a somewhat complicated formula given that you have different # of slots on weekends and weekdays, but… ah whatever, just wrote it for you below.
  2. You can calculate occupied slots between the same two dates
  3. Divide one by another — profit!

You can create a separate table as a list of months and calculate it there, then build a chart out of that table.

Alternatively, of course, you can set up automation or a button to just add some blank entries for missing days. But the first approach is cleaner IMO — no fake data.

Here’s the formula that calculates the overall number of slots for current month, for example. It is assumed that there are 15 slots on weekdays, 20 slots on weekends, but that you can later replace with values from some table:

Sequence(EndOfMonth(Today(), -1) + Days(1), EndOfMonth(Today(), 0)).FormulaMap(
  If(
    CurrentValue.Weekday().In(7, 1),
    20,
    15
  )
).Sum()
2 Likes
3

Dear @Paul_Danyliuk, thank you!!! I have came across FormulMap only a few days ago and now I see it’s power even more.

One small note about Rooms. The intention here was to use Room Type, not specific room index. It so because Big rooms and Small Rooms are the same and we do not distinguish between Small1 and Small2.