Forum Discussion

Manionpower's avatar
Manionpower
New Member
1 year ago
Solved

Reference different tables based on date

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. 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

2 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.