Forum Discussion

H3nning's avatar
H3nning
Helper V
4 years ago
Solved

Find Overlapping Dates

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 ...
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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.

v-angzheng-msft's avatar
v-angzheng-msft
Community Support
4 years ago

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's avatar
H3nning
Helper V
4 years ago

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