I've got 2 tables:
In the 2nd table, it'd like to return the value of the 1st table, based on the newest matching date.
So for "abc123", any date between 01/01/2019 & 31/01/2019 (the day before the next date in table 1) it should return 5.
Hope that makes sense,
try a measure in table2
Value = var _maxDate = CALCULATE(MAX('Table1'[Date]);FILTER(ALL(Table1);Table1[Unique Key]=SELECTEDVALUE(Table2[Unique Key]) && Table1[Date]<SELECTEDVALUE(Table2[Date]))) RETURN calculate(MAX('Table1'[Value]);FILTER(ALL(Table1);Table1[Date]=_maxDate && Table1[Unique Key]=SELECTEDVALUE(Table2[Unique Key])))
do not hesitate to give a kudo to useful posts and mark solutions as solution
I found the solution - at least it looks like it's working 🙂
Using 2 different dax formulas - first one to get the correct date, then one to use that date in a LOOKUPVALUE to get the correct cost price.
Appreciate your help.
No, that doesn't seem to work - could it be because my table 1 could have more than 2 dates? I think the max is about 10, but that only keeps growing.