Flagging Non-consecutive overlapping dates

[digitalPlay]

Hello!
I hope you guys could help me in solving this, I need to flag rows that have clashing date ranges against the rest of the dates for a specific location.
In this example, I am subtracting current row End Date to the next row's Start Date to get the Overlapping days, <=0 will be No Clash otherwise it's a clash. But it only works for next rows, it will overlook the fact that ID 1 and 3 overlaps.
I need a solution where current row value will be checked against all dates per location. Not sure if looping is applicable in m query.

ID    Location        Start Date        End Date           Overlapping Days  Status
1      Scotland     01/07/2025     14/08/2026               0                       No Clash
2      Scotland    29/10/2025      19/11/2025              289                    Clash
3      Scotland    20/11/2025      20/12/2025               -1                     No Clash

[digitalPlay]

@DallasBaba @spinfuzer thank you both for providing brilliant solutions! However, I forgot another key thing for this to work pefectly, I need a dynamic data range parameter so I can only see clashing dates on a specific dates e.g within 3mos, 6mos,etc. I could manually filter in M Query datenow+how many days/months but it will not be dynamic.
I figured this can only be done as a measure?

[spinfuzer]

If you need this to adjust dynamically with something like dates on a pivot table then it needs to be a measure.

[digitalPlay]

it should check the Start Dates per ID per location. If it's within the dates in the slicer, it should only check if it's a clash up to the end of the range. For the example below, without a date parameter, ID 2 will be a clash, but following the date range, it's no clash. 

Range based on Start Dates:
Start    End
01-Jul  02-Aug

ID  Location  Start Date     End Date          Status
1  Scotland   01/07/2025   01/08/2025     No Clash
2  Scotland  02/08/2025    01/11/2025     No Clash
3  Scotland  03/08/2025    20/12/2025     Clash

[digitalPlay]

@spinfuzer  @DallasBaba  thank you both for your responses, I am a bit daft and forgot about the date range parameter. Calculation must flag a clash if it happens within a dynamic date range and I suppose I would need a measure in order to achieve this.

[spinfuzer]

In the approach below, we generate a list of dates for all date ranges.  Next we group by location and then combine the list of dates. .

Now we compare Current Row Date List to (Combined Date List without Current Row's Values)

If there is any matching dates then it clashes.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpOzi/JScxLATINDPUNzPWNDIxMgRxDE30DCxDHTClWJ1rJCFWpkaW+oQFY6aEFIMVAriFEJ0ixMZpiA7gshGOEUGpCirmmmO4FyxoAOcaGUHNNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Location = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date List", each List.Dates([Start Date],Duration.TotalDays([End Date]-[Start Date])+1,#duration(1,0,0,0) ) ),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Location"}, {{"Rows", each
        let dates = List.Combine(_[Date List])
        in  
            Table.AddColumn(_, 
                "Status", 
                (x) => if List.ContainsAny( List.Difference(dates, x[Date List] ), x[Date List]) then "Clash" else "No Clash"
            ) 
        }} ),
    clash = Table.Combine(#"Grouped Rows"[Rows]),
    #"Removed Columns" = Table.RemoveColumns(clash,{"Date List"})
in
    #"Removed Columns"

[DallasBaba]

@digitalPlay You can use the following DAX formula to flag rows that have clashing date ranges against the rest of the dates for a specific location:

Clash = 
VAR CurrentRowEndDate = [End Date]
VAR CurrentRowStartDate = [Start Date]
VAR CurrentRowLocation = [Location]
VAR OverlappingDays = 
    CALCULATE(
        MAX([End Date] - [Start Date] + 1),
        FILTER(
            'Table',
            [Location] = CurrentRowLocation &&
            [Start Date] <= CurrentRowEndDate &&
            [End Date] >= CurrentRowStartDate &&
            [ID] <> EARLIER([ID])
        )
    )
RETURN
    IF(OverlappingDays <= 0, "No Clash", "Clash")

Please note that this formula assumes that the date columns are formatted as dates in your table. If they are not, you may need to adjust the formula accordingly.

 Let me know if this works for you. @ me in replies, or I'll lose your thread!!!  
Note: 

If this post helps, please consider Accepting it as the solution to help others find it more quickly. OR
If my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Best Regards,

Dallas.