Forum Discussion

esclare's avatar
esclare
Frequent Visitor
7 years ago
Solved

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
Need Help
  • AlB's avatar
    AlB
    7 years ago

    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

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    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's avatar
      esclare
      Frequent Visitor
      7 years ago

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

      • esclare's avatar
        esclare
        Frequent Visitor
        7 years ago

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