Get the most recent value from another table with measures

Question

Hello experts,&nbsp;I ran into this question and hope to get your advice on it.&nbsp;I have two tables:&nbsp;Table 1: Item, DateTable 2: Item, Date, Value&nbsp;I want to be able to find the most recent date from Table2 that before Table1 date, and also get the value.I have been tried with Calculated Columns but didn't look right:&nbsp;&nbsp;Column = CALCULATE(&nbsp; &nbsp; MAX(T2[Time]),&nbsp; &nbsp; FILTER(T2,&nbsp; &nbsp; T2[Time] &lt;= T1[Time]))&nbsp;Column 2 = LOOKUPVALUE(T2[Value],T2[Time],T1[Column])&nbsp;I tried measures but but didn't work well.&nbsp;Measure = VAR _maxdate = MAX(T1[Time])VAR _maxitem = MAX(T1[Item])RETURNCALCULATE(&nbsp; &nbsp; MAX(T2[Value]),FILTER(ALL(T2),T2[Time]&lt;=_maxdate&amp;&amp;T2[Item] =_maxitem)&nbsp; &nbsp; )&nbsp;My question is:How to do this by measures? Otherwise how to do it with calcualted columns?&nbsp;Many thanks

amitchandak · Accepted Answer

XR043 , new column in table 1 
&nbsp;
MAX(Filter(T2,T2[Time] =t1[Time] &amp;&amp;T2[Item] =T1[Item]), T2[Value])
&nbsp;
refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to anotherhttps://www.youtube.com/watch?v=Wu1mWxR23jUhttps://www.youtube.com/watch?v=czNHt7UXIe8

Forum Discussion

Get the most recent value from another table with measures

1 Reply

Related content

Find Most Recent Date Relative to another Date Column in Power Query

Cumulative Difference from Another measure

Filter measure by another measure

Use measures from another semantic model

DAX - Average (measure) based on most recent Date