Calculating Daily occupancy from rows with start and end dates

[ihatethis]

I have two tables: the example below and a generic date table. 

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

If I wanted to create a table to show the occupancy per night, how could I go about that with just the date range of check in and check out? Some stays are months long, so any method that pulls all the middle dates out into a column or row wouldn't necessarily be ideal

Output wanted:

Date	Guests
12-31	1
01-01	3
01-02	4
01-03	3

[Zanqueta · Accepted solution]

Hello @ihatethis 

Best Practice Approach

Use a measure that checks if a given date falls within the check-in and checkout range for each guest, and then aggregates across all guests.

---

Step 1: Ensure Relationships

• Your Date table should be marked as a Date Table in Power BI.
• There is no direct relationship between the Date table and the Guest table because the occupancy spans ranges. We will handle this with DAX.

---

Step 2: Create the Measure

Daily Occupancy =
VAR SelectedDate = MAX('Date'[Date])
RETURN
CALCULATE(
    COUNTROWS('GuestTable'),
    FILTER(
        'GuestTable',
        'GuestTable'[Check-In Date] <= SelectedDate &&
        'GuestTable'[Checkout Date] > SelectedDate
    )
)

Official Reference:

COUNTROWS function (DAX) - DAX | Microsoft Learn

CALCULATE function (DAX) - DAX | Microsoft Learn

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

[Jaywant-Thorat · Accepted solution]

This is a classic occupancy / hotel nights problem and it’s 100% solvable with DAX, without exploding dates into rows.

Correct Modeling (Important)

You already have:

• Fact table: Stays (Guest ID, Check-In Date, Checkout Date)

• Date table: Calendar[Date]

Create ONE relationship only:

• Calendar[Date] → Stays[Check-In Date] (active)

(Checkout date will be handled in DAX, not relationships)

DAX Measure: Occupancy per Night

Create this measure:

Guests Occupied =
VAR CurrentDate =
SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( Stays[Guest ID] ),
FILTER (
ALL ( Stays ),
Stays[Check-In Date] <= CurrentDate
&& Stays[Checkout Date] > CurrentDate
)
)

Logic (simple)

• Guest is counted if:

  • Checked in on or before the night

  • Checked out after the night

• Checkout day is not counted (standard hotel logic)

Build the Visual

Create a Table visual:

• Rows → Calendar[Date]

• Values → Guests Occupied

(Optional: filter dates where Guests > 0)

=================================================================
Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Jaywant Thorat | MCT | Data Analytics Coach
LinkedIn: https://www.linkedin.com/in/jaywantthorat/
Join #MissionPowerBIBharat = https://shorturl.at/5ViW9
#MissionPowerBIBharat
LIVE with Jaywant Thorat from 10 Jan 2026
8 Days | 8 Sessions | 1 hr daily | 100% Free

[cengizhanarslan]

Please try the formula below:

Guests (Occupied Nights) =
VAR d = MAX ( DimDate[Date] )
RETURN
CALCULATE (
    DISTINCTCOUNT ( Stays[Guest ID] ),
    FILTER (
        ALL ( Stays ),
        Stays[Check-In Date] <= d
            && Stays[Checkout Date] > d
    )
)

[Zanqueta · Accepted solution]

Hello @ihatethis 

Best Practice Approach

Use a measure that checks if a given date falls within the check-in and checkout range for each guest, and then aggregates across all guests.

---

Step 1: Ensure Relationships

• Your Date table should be marked as a Date Table in Power BI.
• There is no direct relationship between the Date table and the Guest table because the occupancy spans ranges. We will handle this with DAX.

---

Step 2: Create the Measure

Daily Occupancy =
VAR SelectedDate = MAX('Date'[Date])
RETURN
CALCULATE(
    COUNTROWS('GuestTable'),
    FILTER(
        'GuestTable',
        'GuestTable'[Check-In Date] <= SelectedDate &&
        'GuestTable'[Checkout Date] > SelectedDate
    )
)

Official Reference:

COUNTROWS function (DAX) - DAX | Microsoft Learn

CALCULATE function (DAX) - DAX | Microsoft Learn

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

[FBergamaschi]

Hi @ihatethis 

if this is what you are looking for based on the data you gave (hope I interpreted dates in the right way)

you can find my file here

https://drive.google.com/drive/folders/16Qg6qfKEShGp7AEsdnM8CecoV3xD6CiP?usp=drive_link

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI