Average daily Occupancy with only start and end date (on dates WITH occupancy)

[ihatethis]

I have two tables: a Date Table and a Stays Table

The stays table has the guest, the room, and the check-in date, and the check-out date. From a previous post, I had help getting the guests per night across date ranges, but now I'm struggling to get the service dates to calculate the average occupancy per day and per bed. I couldn't use the same filter logic to bring in all the unique dates between the checkin and checkout dates. 

If no one stays in a building, I want to be able to exclude that date from that building's occupancy (we only want to measure occupany when that building is in-use)

Stays Table 

GuestID	Building	Bed ID	Check-In Date	Checkout Date
1	1	100	01/01/2026	01/08/2026
2	2	102	01/01/2026	01/05/2026
3	1	104	12/31/2025	01/10/2026
4	1	106	01/02/2026	01/03/2026
5	2	100	1/08/2026	01/10/2025

Date Table (spans >365 days)

Date

01/01/2026

Etc.

Output Wanted

• Average Daily Occupancy (per building, per date in use)

Building	Average Daily Occupancy
Building 1	1.6 (to round up to 2)
Building 2	1

previous post re: daily occupancy 

[cengizhanarslan · Accepted solution]

Step 1 – Occupancy per building per day

This counts how many beds are occupied on a given date.

Daily Occupancy =
VAR CurrentDate = SELECTEDVALUE ( 'Date'[Date] )
RETURN
CALCULATE (
    DISTINCTCOUNT ( Stays[Bed ID] ),
    FILTER (
        Stays,
        Stays[Check-In Date] <= CurrentDate
            && Stays[Checkout Date] > CurrentDate
    )
)

If you put Date + Building in a table, this will show the correct nightly occupancy.

Step 2 – Average Daily Occupancy (excluding zero days)

Now we average only over dates where occupancy > 0 for that building.

Average Daily Occupancy :=
VAR OccupiedDates =
    FILTER (
        VALUES ( 'Date'[Date] ),
        [Daily Occupancy] > 0
    )
RETURN
AVERAGEX (
    OccupiedDates,
    [Daily Occupancy]
)

[ihatethis]

thank you! this was exactly what I needed!