Find "last" text value within another table, based on Most Recent Date

[qbarnes]

Hello Everyone, 

I'm in need of assistance, as I have two tables that I'm working with...   My Entire_Portfolio_Table has all loans in our portfolio, and my Modification_Table only contains loans that have ever been modified. 

The Modification Table, does consist of multiple Modification Types, for various reasons, throughout the lifecycle of each loan.   I need to pull in the most recent ModType by Date (from the Modification_Table), into my Entire_Portfolio_Table.

Modification_Table

Leadid	ModType	ModDate
0002	PmtHoliday	2/1/2020
0002	SubsequentMod	3/15/2020
0002	PermanentMod	5/14/2020
0003	CovidMOD	4/1/2020
0005	PmtHoliday	1/20/2020
0005	CovidMOd	4/1/2020
0005	PermanentMod	6/1/2020

Entire_Portfolio_Table:

Leadid	DateFunded	ModType	ModDate
0001	1/2/2017	null	null
0002	1/2/2017	PermanentMod	5/14/2020
0003	2/12/2017	CovidMOD	4/1/2020
0004	2/14/2017	null	null
0005	3/1/2017	PermanentMod	6/1/2020

The result i need would be whats shown in the Portfolio Table, above.

Note: My initial DAX formulas found the most recent ModType, but it eliminated the loans that didn't have a mod, but I need a snapshot of all loans:   

[Anonymous · Accepted solution]

Hi @qbarnes ,

Create two column as below.

Column = CALCULATE(MAX('Table'[ModDate]),FILTER('Table','Table'[Leadid]='Table (2)'[Leadid]))

Column 2 = CALCULATE(MAX('Table'[ModType]),FILTER('Table','Table'[ModDate]='Table (2)'[Column]))

Result would be shown as below.

 

Best Regards,

Jay

[qbarnes]

Awesome!!  Thank you so much!!

[Greg_Deckler]

@qbarnes - Sounds like you want Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

[qbarnes]

@Greg_Deckler, 

Thanks for the assist.   I'm not calculating any numeric values, so how would I transform this portion of the formula, and the references to: 

 "__Value",SUM('Table'[Value])

I basically need to pull in all loans where MODType and ModDate are applicable, for the entire portfolio.   If a loan never had a Modfication i need to maintain that as a blank value as well, to have a full snapshot of what does and does not have a ModType.