How to lookup different criteria and return results from another columns

[jp_comms]

Hi Everyone,

I'm trying to get the results of the following. but got stuck using lookup. any suggestions please.

Whenever source file is refreshed daily, aging for each Invoice ID are also updated.

From the latest report date, I will Look up "Invoice ID and Aging" from previous Report Date and using Updated Invoice ID and Aging. if found, return the Invoice ID and aging.

Invoice ID and Aging	Updated Invoice ID and Aging	Report Date	Result
21227365-1Past Due		03/08/2025
21215357-1Current	21215357-1Past Due	03/08/2025
21227359-1Current	21227359-1Past Due	03/08/2025
21228458-1Future	21228458-1Current	03/08/2025
21228437-1Current	21228437-1Current	03/08/2025
21228456-1Future	21228456-1Future	03/08/2025
21227365-1Past Due		03/08/2025
21215357-1Past Due	21215357-1Past Due	04/08/2025	21215357-1Past Due
21227359-1Past Due	21227359-1Past Due	04/08/2025	21227359-1Past Due
21228458-1Current	21228458-1Past Due	04/08/2025	21228458-1Current
21228437-1Current	21228437-1Current	04/08/2025	21228437-1Current
21228456-1Current	21228456-1Future	04/08/2025	21228456-1Current
21228453-1Current		04/08/2025	blank
21228472-1Current		04/08/2025	blank
21228459-1Current		04/08/2025	blank
21228485-1Current		04/08/2025	blank
21228454-1Current		04/08/2025	blank
21228471-1Current		04/08/2025	blank

Thank you

[GeraldGEmerick · Accepted solution]

@jp_comms I believe something like this should work:

Column = 
VAR _ReportDate = [Report Date]
VAR _InvoiceIDandAging = [Invoice ID and Aging]
VAR _Return = CALCULATE( MAX( 'Table'[Updated Invoice ID and Aging] ), FILTER( 'Table', 'Table'[Report Date] < _ReportDate && [Updated Invoice ID and Aging] = _InvoiceIDandAging ) )
RETURN _Return

[jp_comms]

thank you so much, it worked! 😉