Forum Discussion
Find Overlapping Dates
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 ZhengIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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?
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?