Forum Discussion

patrick3's avatar
patrick3
Helper II
6 years ago

Return value based on Date / Key pair

Hi all,   I've got 2 tables:   Date Unique Key Value 01/01/2019 abc123 5 01/02/2019 abc123 10 01/01/2019 abc456 5 01/02/2019 abc456 10   Date Unique Key Value ...
Need Help
patrick3's avatar
patrick3
Helper II
6 years ago
Ah you're right of course, just a typo on my end
az38's avatar
az38
Community Champion
6 years ago

patrick3 

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

  • patrick3's avatar
    patrick3
    Helper II
    6 years ago

    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.

  • patrick3's avatar
    patrick3
    Helper II
    6 years ago

    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.