cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
esclare Frequent Visitor
Frequent Visitor

get value based on max date and match

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
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: get value based on max date and match

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]
    )

 

3 REPLIES 3
Highlighted
Super User
Super User

Re: get value based on max date and match

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 Frequent Visitor
Frequent Visitor

Re: get value based on max date and match

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

esclare Frequent Visitor
Frequent Visitor

Re: get value based on max date and match

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] )
)