Sort on Start date / End date not following

[Anonymous]

Hi,

Hopefully someone can send me in the right direction, I have tried to search for some terms but I cant seem to find what I want.

I have a large set with contractnrs and start/end dates:

I want to filter out the orange Codes because there is only 1 row and I want to keep the values in Code where the same number is > 2.

The green dates are correct because 27-6-2022 and 28-6-2022 are following. (Values in column Code have the same numbers)

The red dates are not correct because there are some dates missing in between. (Values in column Code have the same numbers)

How can I filter only on dates that have missing dates in between (like the red dates)?

Thank you!

 

[ryan_mayu · Accepted solution]

@Anonymous 

maybe you can create a new column

ifmissing = 
VAR _next=minx(FILTER('Table','Table'[code]=EARLIER('Table'[code])&&'Table'[start]>EARLIER('Table'[end])),'Table'[start])
RETURN if(ISBLANK(_next)||_next='Table'[end]+1,blank(),"Y")

pls see the attachment below

[Anonymous]

Thanks, that looks pretty advanced!

Unfortunately when i use your pbix and statement I get an error:

I solved it my creating a new column not in Power Query Editor, but in the table visualization.

Thanks! I will try to use it in my own pbix.

update:

It works in my own pbix as well.

Thanks so much!

[ryan_mayu]

you are welcome