Find Overlapping Dates

[H3nning]

Hi,

I'm struggling with overlapping dates. I found some solutions but they are not producing the expected result.

I have a table of employees. In that table I have scd type 2. So for Example when someone get married there are to rows, each with a valid_from and valid_to date:

table:

ID	Name	valid_from	valid_to
1	Peter Parker	2020-01-01	2020-12-31
1	Peter Pan	2021-01-01	2121-01-01
2	Clark Kennt	2020-01-01	2020-12-31
2	Clark Kent	2020-01-01	2121-01-01

I want to find data errors in that table usin Power Query in Power BI. I want to Display all IDs with overlapping dates. In the example above that would be "2".

How can I do that?

What I tried so far:

Approach1 (which don't fully understand):

Overlapping =

VAR _start = table[valid_from]

VAR _end = table[valid_to]

VAR _result = COUNTROWS(

FILTER(

table,

table[ID] = EARLIER(table[ID]) &&

(

(_start >= ptable[valid_from] && _start <= table[valid_to])

)

))

RETURN _result - 1

This returns numbers, but I expected it to be 0 when not overlapping. But that is not the case. I got the solution from the community and I just tried it.

Approach 2:

I added a column in Power Query with the Substract Dates function. Then I get the date difference between valid from and valid to. Then I add another column with this:

List.Dates ([valid_from],[DateDiff]+1,#duration(1,0,0,0))

the result I expanded to all rows. Afterwards I wanted to group by ListofDates and ID. But then I get the error 

Expression.Error: The 'increment' argument is out of range. Details: 1.00:00:00

That was the point when I decided to ask here, so hopefully someone could help out? That would be great 🙂 Thanks in advance!

[v-angzheng-msft · Accepted solution]

Hi, @H3nning 

Try to create measures like below:

_Measure = 
var _current_ValidTo=MAX('Table'[Vaild To])

var _t=FILTER(
    FILTER(ALL('Table'),'Table'[ID]=Max('Table'[ID])&&[Name]<>Max('Table'[Name])),
    _current_ValidTo<=[Vaild To]&&
    _current_ValidTo>=[Valid From]
    )
var _count=COUNTROWS(_t)

return _count

_result = IF(
SUMX(FILTER(ALL('Table'),[ID]=MAX('Table'[ID])),[_Measure])>=1,1,0)

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[H3nning]

This really works, thank you. My next big task will be to understand why... 🙂

[Anonymous]

For your column `ID`, is that a user's Unique ID? Meaning each user has 2 entries?

> In the example above that would be "2".

2 what? User number 2? 2 invalid ranges?

Do you want to match users against each other, or can their own records over lap?

[H3nning]

Hi, ID is not unique. In reality it is a simplyfied Name like PPARKER. I wanted to make it easier here, that was obviously unsuccessful 😉 It connects the different Entries. Like I said it is a slowly changing dimension type 2 and each entry has a valid from and valid to date. In the example Peter Parker was renamed to Peter Pan, but that is only valid from 2022. 

These Entries are in parts made by manual input. Therefore i want to provide a dashboard where unwanted errors due to overlapping date ranges occur. Only the overlap within one ID is relevant.

The "2" you asked for, is the ID of the last two rows.  I want to display all IDs with overlapping dates. In the example above that would be "2". The date range there is overlapping. That is not the case in the first two rows. For my purpose I could work with different outcomes. One would be a table of all IDs with overlapping date ranges, In my example that would be the ID "2", because 1 has no overlapping entries. It would also be sufficient to just filter down the list with all entries to those with overlapping date ranges. I could work with that too.

Maybe it is possible to add a binary column at the end of the list with 0= no overlapping entries for this ID and 1= overlapping entries within this ID. Preferred outcome then would be:

ID	Name	Valid From	Vaild To	Overlap
1	Peter Parker	2021-01-01	2021-12-31	0
1	Peter Pan	2022-01-01	2122-01-01	0
2	Clark Kennt	2021-01-01	2021-12-31	1
2	Clark Kent	2021-01-01	2122-01-01	1

How to set up column "Overlap"?

Could i clarify?