Forum Discussion

New Member

7 months ago

Solved

Calculating Daily occupancy from rows with start and end dates

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

Need Help

Zanqueta
7 months ago
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
7 months ago
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

4 Replies

FBergamaschi
Super User
7 months ago
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
Zanqueta
Super User
7 months ago
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 🌀.

cengizhanarslan

Super User

7 months ago

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
    )
)

Jaywant-Thorat
Super User
7 months ago
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

Forum Discussion

Calculating Daily occupancy from rows with start and end dates

Best Practice Approach

Step 1: Ensure Relationships

Step 2: Create the Measure

Correct Modeling (Important)

DAX Measure: Occupancy per Night

Logic (simple)

Build the Visual

4 Replies

Best Practice Approach

Step 1: Ensure Relationships

Step 2: Create the Measure

Correct Modeling (Important)

DAX Measure: Occupancy per Night

Logic (simple)

Build the Visual

Related content

Calculating Daily Occupancy rate of hotel

Improve speed in occupancy calculation

Daily Occupancy Average

warehouse occupancy on daily basis

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