Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

join table but only most recent value

I need help. Can be DAX or M. I have table1 with code and date (and more irrelevant columns). I have table2 with the same code and date, but also a value that changes from time to time. I need to ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi Anonymous,

     

    Try this DAX formula, please. 

    Column =
VAR currentCode = [code]
VAR currentDate = [date]
VAR maxDateOfTable2 =
    CALCULATE (
        MAX ( 'Table2'[date] ),
        'Table2'[date] <= currentDate,
        Table2[code] = currentCode
    )
RETURN
    LOOKUPVALUE (
        Table2[value],
        Table2[code], currentCode,
        Table2[date], maxDateOfTable2
    )

    join_table_but_only_most_recent_value

     

    Best Regards,

    Dale

Anonymous's avatar
Anonymous
Not applicable
8 years ago

I found a way, but I don´t think it is a nice solution.

I got my table1 and duplicated it.

Remove all columns but code and date.

Then, I append it below table2, like this:

 

 

This is the new table2.

Then I created a new field which is: code+yyyy+MM+dd.

Order it ascending.

Use the Fill Down option from the Transform ribbon.

vòila!

Now I have a table like this below. Numbers in red are the Fill Down result.

 

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    Try this DAX formula, please. 

    Column =
VAR currentCode = [code]
VAR currentDate = [date]
VAR maxDateOfTable2 =
    CALCULATE (
        MAX ( 'Table2'[date] ),
        'Table2'[date] <= currentDate,
        Table2[code] = currentCode
    )
RETURN
    LOOKUPVALUE (
        Table2[value],
        Table2[code], currentCode,
        Table2[date], maxDateOfTable2
    )

    join_table_but_only_most_recent_value

     

    Best Regards,

    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      yeah! it worked! thanks a lot!