Add lookup data from another table/column using custom columns

[ksummers68]

I have a Pay Run ID table

Pay Run ID (Year/Pay Period)	Pay Begin Date	Pay Period End Date
501	2004-12-20	2005-01-02
502	2005-01-03	2005-01-16
503	2005-01-17	2005-01-30

I have a requisition table with Created Date

Requisition #	Req. Title	Date Created
27629	Test A	1/30/2005
27629	Test A	1/30/2005
27630	Test C	12/22/2004

I want to add a column to the requisition table and display the Payrun ID

Requisition #	Req. Title	Date Created	Payrun ID
27629	Test A	1/30/2005	503
27629	Test A	1/30/2005	503
27630	Test C	12/22/2004	501

[parry2k · Accepted solution]

your output seems to be wrong, 503 will never get return because pay run doesn't have that date range.

Here is the solution thou, add new measure in your requistion table:

PayId = 
var r = MAX(Req[Date Created]) 
return Calculate(Max(PayRun[Pay Run ID (Year/Pay Period)]), Filter(
All(PayRun), 
r >= PayRun[Pay Begin Date] && r <= PayRun[Pay Period End Date]
)
)

[parry2k · Accepted solution]

your output seems to be wrong, 503 will never get return because pay run doesn't have that date range.

Here is the solution thou, add new measure in your requistion table:

PayId = 
var r = MAX(Req[Date Created]) 
return Calculate(Max(PayRun[Pay Run ID (Year/Pay Period)]), Filter(
All(PayRun), 
r >= PayRun[Pay Begin Date] && r <= PayRun[Pay Period End Date]
)
)

[ksummers68]

You are correct I had the wrong year (I corrected it in the example) I will try this, thank you very much!

[v-yulgu-msft · Accepted solution]

Hi @ksummers68,

parry2k's solution works great in a measure. If you would like to add a column in source table, you just need to make a little modification:

PayId =
CALCULATE (
    MAX ( 'Pay Run ID'[Pay Run ID (Year/Pay Period)] ),
    FILTER (
        ALL ( 'Pay Run ID' ),
        requisition[Date Created] >= 'Pay Run ID'[Pay Begin Date]
            && requisition[Date Created] <= 'Pay Run ID'[Pay Period End Date]
    )
)

Best regards,

Yuliana Gu