How to check if row of time ranges overlap with other rows (and multiple condition)

[francescolar]

Hi everyone!

I'm new to Power BI!
I'm trying to create a "binary" column that should set the value 1 if two conditions are present simultaneously: the period is overlapped to others in the table (ex. row 1 and 6) and the production period in that row is longer.

As an example of my data.

Machinery	Date	Start_prod	End_prod	Minuts_prod	Check Overlap
A	01/02/2019	01/02/2019 08:00	01/02/2019 09:10	70	1
A	01/02/2019	01/02/2019 09:00	01/02/2019 09:10	10	0
B	01/02/2019	01/02/2019 08:00	01/02/2019 12:00	240	0
B	01/02/2019	01/02/2019 13:00	01/02/2019 14:00	60	0
A	01/02/2019	01/02/2019 12:00	01/02/2019 13:00	60	0
A	02/02/2019	02/02/2019 08:00	02/02/2019 09:10	70	1
A	02/02/2019	02/02/2019 08:30	02/02/2019 09:10	40	0

Can you help me please? Thanks in advance!

[francescolar · Accepted solution]

Autofind solution 🙂

Period Overlap =
      VAR Overlapped =
              COUNTROWS(
                      FILTER(
                               ALL(Table);
                               Table[Start_prod] <= EARLIER(Table[Start_prod]) &&
                               Table[End_prod] >= EARLIER(Table[End_prod]) &&
                               Table[Date] = EARLIER(Table[Date]) &&
                               Table[Machinery] = EARLIER(Table[Machinery]) &&
                               Table[Id] <> EARLIER(Table[Id])
                               )
                    )
RETURN
IF(Overlapped; "YES"; "NO")