How can I calculate the number of times dates in a dataset overlap?

[dehmos27]

I have a dataset similar to the following:

Event	Start	Final
Abc	1/1/2020	1/30/2020
Def	1/15/2020	2/14/2020
Ghi	3/1/2020	3/30/2020
Jkl	3/5/2020	4/4/2020
Mno	3/17/2020	4/16/2020
Pqr	4/1/2020	4/30/2020

I want to find out the number of times a date range falls on or between the start and end dates of other events in the dataset.

In this scenario, I'd like to create a table that shows the following:

Month	Overlaps
1	2
2	0
3	3
4	3

[Anonymous]

Hi @dehmos27 ,

The "My" field is month number? And what's the calculation logic of field "Traslapos"?

Best Regards

Rena

[edhans]

Try this in Power Query. It looks at the dates and sees how many are between the other ranges. It then subtracts 1 from that result because it always overlaps with itself.

It returns this table:

The key function is this:

let 
    varStart = [Start], 
    varEnd = [Final] 
in
    Table.RowCount(
        Table.SelectRows(
            #"Changed Type", 
            each (varStart >= [Start] 
                and varStart <= [Final] )
            or (varEnd >= [Start] 
                and varEnd <= [Final] )
            )
        ) -1

The full code is below. Put that in a blank query:

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxKVtJRMtQ31DcyMDIAM40NIOxYnWgll9Q0iLQpTN5I39AEIe+ekQkUM0ZoN0bR7pWdAxaD6zbRR9Lsm5cP0WyOkDY0Q8gHFBZBxBDScMNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Event = _t, Start = _t, Final = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Event", type text}, {"Start", type date}, {"Final", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let 
    varStart = [Start], 
    varEnd = [Final] 
in
    Table.RowCount(
        Table.SelectRows(
            #"Changed Type", 
            each (varStart >= [Start] 
                and varStart <= [Final] )
            or (varEnd >= [Start] 
                and varEnd <= [Final] )
            )
        ) -1)
in
    #"Added Custom"

[edhans]

Here is the same thing using a measure. I think you'd hit performance issues with millions of records with this, or even tens of thousands possibly, but see how it works for you. I'm at a toss up as to whether Power Query or DAX is the best place for this. Depends on your use case. The [Custom] column is what was generated from Power Query and you can see the results are the same.

Overlap Count =
VAR StartDate =
    MAX( 'Table'[Start] )
VAR EndDate =
    MAX( 'Table'[Final] )
VAR Overlaps =
    // returns table with all rows that have overlaps
    FILTER(
        ALL( 'Table' ),
        ( StartDate >= 'Table'[Start]
            && StartDate <= 'Table'[Final] )
            || ( EndDate >= 'Table'[Start]
            && EndDate <= 'Table'[Final] )
    )
VAR CountOverLaps =
    /*  Counts the records in the OverLaps table and subtracts one since there is
    always one match when the record matches itself. */
    COUNTROWS( Overlaps ) - 1
RETURN
    CountOverLaps

[mahoneypat]

Not totally sure I understood your request, but I think the My column in your visual was for Month-Year.  If not, maybe you can adapt this approach.  This assumes you have a Date table with a MonthYear column, and there is no relationship to the Events table with your example data.

Make a table with the Date[MonthYear] column and this measure

Overlap = var mindate = MIN('Date'[Date])

var maxdate = MAX('Date'[Date])

return CALCULATE(COUNTROWS(Events), Events[Final]>=mindate, Events[Start]<=maxdate)+0

Which yields this table.  The February # is different from yours though. I'm hoping that was a typo.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat