Reference different tables based on date

Thanks for the concern from danextian.

Hi Manionpower ,

Based on your problem description I created simple data:

Merge the two tables.

The relationship is shown in the figure:

Create measure:

BeforeDateResponsible = 
CALCULATE(
    CONCATENATEX(
        VALUES('Table'[responsible person]),
       'Table'[responsible person],
        ", ",
        'Table'[responsible person],
        ASC
    ),
    FILTER(
        'Table',
        'Table'[Date of approval] <= MAX('Date'[Date])
    )
)

AfterDateResponsible = 
CALCULATE(
    CONCATENATEX(
        VALUES('Table'[responsible person]),
        'Table'[responsible person],
        ", ",
        'Table'[responsible person],
        ASC
    ),
    FILTER(
        'Table',
        'Table'[Date of approval] > MAX('Date'[Date])
    )
)

Combined these two measures and added logic: no duplicate display if there is no change in personnel:

CombinedResponsible = 
VAR _beforeDateResponsible = 
    CALCULATE(
        CONCATENATEX(
            VALUES('Table'[responsible person]),
            'Table'[responsible person],
            ", ",
            'Table'[responsible person],
            ASC
        ),
        FILTER(
            'Table',
            'Table'[Date of approval] <= MAX('Date'[Date])
        )
    )
VAR _aftertable = CALCULATETABLE(
    SELECTCOLUMNS(
        FILTER(
            'Table',
            'Table'[Date of approval] > MAX('Date'[Date])
        ),
        'Table'[responsible person]
    )
)
VAR _beforetable = CALCULATETABLE(
    SELECTCOLUMNS(
        FILTER(
            'Table',
            'Table'[Date of approval] <= MAX('Date'[Date])
        ),
        'Table'[responsible person]
    )
)
VAR _except = EXCEPT(_aftertable, _beforetable)
VAR _afterDateResponsible = 
    CALCULATE(
        CONCATENATEX(
            VALUES('Table'[responsible person]),
            'Table'[responsible person],
            ", ",
            'Table'[responsible person],
            ASC
        ),
        'Table'[responsible person] IN _except
    )
RETURN IF(_afterDateResponsible<>BLANK(),_beforeDateResponsible & "&" & _afterDateResponsible,_beforeDateResponsible)

Result:

Best Regards,
Zhu

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