get value based on max date and match

[esclare]

Hi,

first post here.

I have 2 tables.

table1

ID_house  Value    Date

1                 200    01/02/2019

1                   50    01/01/2019

2                 100    02/02/2019

3                   75    04/02/2019

2                   50    01/03/2019

3                   50    03/01/2019

table2
ID_house
1

2

3

I want create a new column in table2 with Value of table1 of max date matching ID of table2

Expected output

table2
ID_house  Value
1               200

2                50

3                75

Thanks

[AlB · Accepted solution]

Hi @esclare 

Try this for your new calculated column in Table2. See it working in this file

NewColValue =
VAR _MaxDate =
    CALCULATE (
        MAX ( Table1[Date] ),
        FILTER ( Table1, Table1[ID_House] = Table2[ID_House] )
    )
RETURN
    LOOKUPVALUE (
        Table1[Value],
        Table1[Date], _MaxDate,
        Table1[ID_House], Table2[ID_House]
    )

[esclare]

Thanks @AlB but didn't work: "A table of multiple values was supplied where a single value was expected."

[esclare]

I introduced a change because I have blank values:

VAR _MaxDate =
    CALCULATE (
        MAX ( table1[date] );
        FILTER ( vavue; table1[id] = table2[id] )
    )
RETURN
    CALCULATE (
    FIRSTNONBLANK ( table1[value]; 1 );
    FILTER ( ALL ( table1); table1[date] = _MaxDate && table1[id] = table2[id] )
)