Get most recent values on new table column

nickchobotar tried your solution, it does not bring the latest Value as per Date, only the first it finds. Need to work one that also distinguishes bettwen dates and takes the latest one to bring in the corresponding value.

---

nickchobotar wrote:

satlasg

Could you please try my version.  It appears you have a ton of rows, so I am swapping iteration with the set logic.
By the way,  the code works both as calc column and a measure.

 

=
CALCULATE (
    MAX ( Table1[Value] ),
    INTERSECT ( 
    		VALUES ( Table1[ID] ), 
    		VALUES ( Table2[ID] ) 
	)
)

 

 

Thanks, Nick -

---

satlasg

Please try this option. Works both as calc column and measure.

ColumnName = 
CALCULATE(
    VALUES(Table1[Value]),
    LASTDATE( Table1[Date])
)

Thanks, Nick -

nickchobotar

I am getting the following when using the formula you provided as Column:

---

Please try this option. Works both as calc column and measure.

 

ColumnName = 
CALCULATE(
    VALUES(Table1[Value]),
    LASTDATE( Table1[Date])
)

 

 

 

---

satlasg

Please make sure you have no ID duplicates in your Table B( Table 2)

Thanks, N -

nickchobotar

There are no ID duplicates in Table B (Table 2), i double-checked through Count and Distinct Count, they have the same number as a result. So that is not the issue.

Keep looking around, thanks.

satlasg

Are you relating both tables on the ID field  or you have other keys ?

Any chance, we can see the model diagram ?

N -

nickchobotar you are right, apologies.

However, i have tested your code and that is where i ended:

---

satlasg wrote:

nickchobotar

 

I am getting the following when using the formula you provided as Column:

 

 

 

---

Please try this option. Works both as calc column and measure.

 

ColumnName = 
CALCULATE(
    VALUES(Table1[Value]),
    LASTDATE( Table1[Date])
)

 

 

 

 

---

 

---

I also checked the IDs and there are no duplicates., cannot figure where the "single value was expected" comes from.

Thanks for the pbix file.

Will try to implement the M code and see where it ends.

G