Date Overlap Check and Evaluate Validity of Overlap

[bcoro]

If I have the following information in the same table: 

I want to know how to show where an overlap occurs and be able to evaluate if the overlap is valid or invalid.  I've already checked that the dates were valid with the calculated column did this to evaluate if the check-in was valid based on a datediff being negative.  Valid Check In = SWITCH(TRUE(),Data[Hotel Stay]>=0,TRUE(),FALSE())

Let's say a person checked into 4 different hotels.

• If the check-in and check-out dates overlap with another, I want it to indicate an overlap;

• An Exception would be if the different CKIDs show the check-in and check-out are on the same date, that wouldn't be considered an overlap because the person is leaving the establishment on the checkout date. 

Then I would like to create a rule that evaluates the validity of the overlap.

• Same hotel with an overlap = invalid
• Differnt hotel with an overlap = valid 

Would this be better as calculated columns in DAX or would there be a better way in Power Query? 

ID	CKID	Name	Check In	Check Out	Overlap	Overlap Status
1	20	Hotel A	1/1/25	1/30/25	Yes	In Valid
1	21	Hotel A	1/1/25	1/31/25	Yes	In Valid
1	22	Hotel B	12/1/24	1/2/25	Yes	Valid
1	23	Hotel C	1/31/25	NULL	No
1	24	Hotel D	11/1/24	11/30/24	No

[v-sshirivolu · Accepted solution]

Hi @bcoro ,

Thanks for reaching out to the Microsoft fabric community forum.

Power Query is ideal for row-wise comparisons and offers better control and clarity when detecting overlapping date ranges across multiple rows.

1. Load the data into Power BI and open Power Query Editor.
    
2. Duplicate the query: Hostel Stays > Duplicate, rename the copy to Hostel Stays Compare.
    
3. Add index columns to both HotelStays and HotelStays_Compare :
   Add Column > Index Column > From 0.
    
4. Merge queries:
   • In the original HotelStays, go to Home > Merge Queries > Merge Queries as New.
   • Join on the ID column in both tables using Inner Join.
   • Name the new query HotelOverlapCheck.
      
5. Remove self-comparisons:
   Add a column:
   [Index] <> [Other Index].
    
6. Add a custom column IsOverlap :
   [Check In] < [Other Check Out] and [Check Out] > [Other Check In]
    
7. Add a column Overlap Status:
   if [IsOverlap] = false then "No" 
   else if [Name] = [Other Name] then "Invalid" 
   else "Valid"
    

If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You!

[bcoro · Accepted solution]

Thank you @v-sshirivolu,

I also need to account for null situations, and it being based on the same resident ID. 

• Resident ID 123 should indicate an overlap
• Resident ID 50 should not indicate an overlap

Currently, I have a couple of records that are showing incorrect:

ID 123, Stay ID 1304, check in 3/11/17, check out null 

ID 123, Stay ID 1305, check in 3/1/17, check out null

ID 502, Stay ID 136, check in 1/4/23, check out 1/9/23

ID 502, Stay ID 17, check in 5/20/21, check out 6/1/21

[rohit1991]

Hi @bcoro ,

Date overlap checks can get surprisingly tricky, especially with ongoing stays and the need to only compare within the same Resident ID. Power Query is definitely the way to go here! It’s much easier for row-by-row logic like this compared to DAX. Here’s how I’d approach it:

Step-by-Step Solution (Power Query / M)

1. Load your table into Power Query. Then, duplicate your table (let’s call one “Main” and the other “Compare”).
   
   

2. Add an Index column to both tables, this helps you filter out “self” matches after merging.
   
   

3. Merge the tables: Use an inner join on ResidentID (so only the same person’s stays are compared). You’ll get every possible pair for each resident.
   
   

4. Remove rows where the Indexes match (this skips a row comparing itself).
   
   

5. Handle ongoing stays: Whenever “Check Out” is null, use today’s date (DateTime.Date(DateTime.LocalNow())). You can use a custom column for this, like: 
   ActualCheckOut = if [Check Out] = null then DateTime.Date(DateTime.LocalNow()) else [Check Out]

6. Overlap logic: Add a custom column like this:

    IsOverlap = ([Check In] < [Compare.ActualCheckOut])
   and ([Compare.Check In] < [ActualCheckOut])
   

[v-sshirivolu · Accepted solution]

Hi @bcoro ,

Thanks for reaching out to the Microsoft fabric community forum.

Power Query is ideal for row-wise comparisons and offers better control and clarity when detecting overlapping date ranges across multiple rows.

1. Load the data into Power BI and open Power Query Editor.
    
2. Duplicate the query: Hostel Stays > Duplicate, rename the copy to Hostel Stays Compare.
    
3. Add index columns to both HotelStays and HotelStays_Compare :
   Add Column > Index Column > From 0.
    
4. Merge queries:
   • In the original HotelStays, go to Home > Merge Queries > Merge Queries as New.
   • Join on the ID column in both tables using Inner Join.
   • Name the new query HotelOverlapCheck.
      
5. Remove self-comparisons:
   Add a column:
   [Index] <> [Other Index].
    
6. Add a custom column IsOverlap :
   [Check In] < [Other Check Out] and [Check Out] > [Other Check In]
    
7. Add a column Overlap Status:
   if [IsOverlap] = false then "No" 
   else if [Name] = [Other Name] then "Invalid" 
   else "Valid"
    

If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You!

[bcoro · Accepted solution]

Thank you @v-sshirivolu,

I also need to account for null situations, and it being based on the same resident ID. 

• Resident ID 123 should indicate an overlap
• Resident ID 50 should not indicate an overlap

Currently, I have a couple of records that are showing incorrect:

ID 123, Stay ID 1304, check in 3/11/17, check out null 

ID 123, Stay ID 1305, check in 3/1/17, check out null

ID 502, Stay ID 136, check in 1/4/23, check out 1/9/23

ID 502, Stay ID 17, check in 5/20/21, check out 6/1/21

[v-sshirivolu · Accepted solution]

Hi @bcoro !

1)To fix this, treat null check-outs as today’s date so ongoing stays count correctly. Update your 'IsOverlap' formula like this:

IsOverlap = 
[Check In] < (if [Other Check Out] = null then DateTime.Date(DateTime.LocalNow()) else [Other Check Out]) 
and 
(if [Check Out] = null then DateTime.Date(DateTime.LocalNow()) else [Check Out]) > [Other Check In]

2)Also, filter to compare only stays with the same Resident ID to avoid false overlaps by adding a column:

SameResident = ([Resident ID] = [Other Resident ID])

and keep only rows where SameResident = true.

3)Update the overlap duration calculation similarly to handle nulls:

OverlapDuration = 
if [IsOverlap] = true then
   Duration.Days(
     Duration.From(
       Number.From(Date.Min(
         if [Check Out] = null then DateTime.Date(DateTime.LocalNow()) else [Check Out], 
         if [Other Check Out] = null then DateTime.Date(DateTime.LocalNow()) else [Other Check Out])
       ) 
       - Number.From(Date.Max([Check In], [Other Check In]))
     )
   )
else 
   null

4)Keep your existing overlap status logic but apply it after these changes:

Refined Overlap Status = 
if [IsOverlap] = false then 
   "No Overlap" 
else if [Name] = [Other Name] then 
   "Invalid - Same Name Overlap" 
else if [OverlapDuration] > 3 then 
   "Invalid - Overlap Too Long" 
else 
   "Valid Overlap"

This should resolve the issues you shared.

If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You!

[ryan_mayu]

what if we have 23 Hotel C  1/31/25 to null and we have another record 1/28/25 to null or 2/1/25 to null?

will that be a overlap? or only if start date is not the same and end date is null ,then we will consider it as not overlapped.

[bcoro]

@ryan_mayu The check-in date will never be null. I put some examples here because it should be based on the individual staying at the hotel, not just the dates. 

OVERLAP

• ID 123, Stay ID 1304, check in 3/11/17, check out null 
• ID 123, Stay ID 1305, check in 3/1/17, check out null

NO OVERLAP

• ID 502, Stay ID 136, check in 1/4/23, check out 1/9/23
• ID 502, Stay ID 17, check in 5/20/21, check out 6/1/21
• ID 502, Stay ID 12, check in 5/1/21, check out 5/20/21

[ryan_mayu]

ID is the customer ， stay ID is the hotel  id  or room ID? 

so only if the check out is null, then all same ID will be overlap. Am I correct?