Select Latest ID From Slow Changing Dimension Table Based On Date Slicer

Hi all , 

Anonymous , thanks for your concern about this case, I have some additions for problem solving.

alee5210 , thank you for giving a specific description of the problem. Based on your imformation, I will give you the following steps to solve the problem.

1.The test table Data table, Date table, EffectiveDate.

2.The test model.

3.New Measure FILTER, and input:

FILTER = 
VAR _select = SELECTEDVALUE(EffectiveDate[Date])
VAR _End =
CALCULATE(
    MAX('Data table'[Effective End Date]),
    ALLEXCEPT('Data table', 'Data table'[Test ID]),
    'Data table'[Effective Start Date] <= _select
)
RETURN
IF(
    MAX('Data table'[Effective Start Date]) <= _select && MAX('Data table'[Effective End Date]) = _End,
    1,
    0
)

4.Add Date to Field.

5.Add FILTER to Filters on this visual.

6.The outcome is in the following pictures.

If they select the user selects 2 Jan, 2024

If they select 16 Jan, 2024

If they select 31 Jan, 2024

All above contents can answer your original questions. For your latest questions, it probably due to the relationship you have between the Data table and the Date table. When the relationship looks like this picture, the same result will appear as you.

Best Regards,

Caroline Mei

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