Reference different tables based on date

[Manionpower]

Hi Everyone,

I have a situation. I have a bunch of invoices which go through a series of approvals from different guys. So, naturally, i made a lookup tabel for the approval process. Now, some guys from the process left the company, and now new guys will replace them in the process. I want my report to show the names of ex-approvers before date dd-mm-yyy and name of new guys after that date. How can this be done?

Sample of my lookup table 

So just imagine guy 16 and guy 17 being replaced by Guy 20 and Guy 21 on date dd-mm-yyy. 

[Anonymous · Accepted solution]

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.

[danextian]

Hi @Manionpower 

This is most likely possible but no enough data to test. So you have those responsible guys when will their responsibility start and how do you intend to visualize or add this to your data? Please post a workable sample data  (not an image) and your expected result from that.