Forum Discussion
Calculating Daily occupancy from rows with start and end dates
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: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 π.
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
Super UserThis 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