Forum Discussion
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 insert a new column in table1, getting the value from table2, but only the values from the most recent date.
This new value on table2 from the most recent date might be higher or lower than the previous values.
On the example below, code A stays the same, code B goes higher from 4 to 16, but code C goes lower from 8 to 2.
It could change a lot of times, not only 2 times like this example.
See that the dates don´t match between tables. Table2 starts on Jan/01 but Table1 starts on Jan/10.
Dates and codes combined from Table1 are not distinct.
Dates and codes combined from Table2 are distinct.
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 )Best Regards,
Dale
3 Replies
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-msftMicrosoft Employee
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 )Best Regards,
Dale