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

     

AlB's avatar
AlB
Icon for Community Champion rankCommunity 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] )
)