Finding Duplicate ID's based on an overlapping start and end date

I have a table with a column of IDs with a start and end date for those IDs. I need a DAX formula that will identify duplicate IDs but only if the start and end dates overlap. For example on a...

Need Help

Ashish_Mathur
8 years ago
Hi,

This is the calculated column formula i wrote

=if(ISNUMBER(CALCULATE(MAX(Data[Start Date]),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Start Date]<=EARLIER(Data[End Date])&&Data[Start Date]>EARLIER(Data[Start Date])))+CALCULATE(MIN(Data[End Date]),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[End Date]>=EARLIER(Data[Start Date])&&Data[End Date]<>EARLIER(Data[End Date])&&Data[Start Date]<=EARLIER(Data[End Date])))),"Duplicate","OK!")

Hope this helps.

Ashish_Mathur

Super User

8 years ago

Hi,

This is the calculated column formula i wrote

=if(ISNUMBER(CALCULATE(MAX(Data[Start Date]),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Start Date]<=EARLIER(Data[End Date])&&Data[Start Date]>EARLIER(Data[Start Date])))+CALCULATE(MIN(Data[End Date]),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[End Date]>=EARLIER(Data[Start Date])&&Data[End Date]<>EARLIER(Data[End Date])&&Data[Start Date]<=EARLIER(Data[End Date])))),"Duplicate","OK!")

Hope this helps.

PowerQueryFTW

Frequent Visitor

8 years ago

Thank you very much!

Ashish_Mathur
Super User
8 years ago
You are welcome.

Forum Discussion

Finding Duplicate ID's based on an overlapping start and end date