cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## 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 05/01/2019 abc123 5 05/02/2019 abc123 10 10/02/2019 abc123 10 15/02/2019 abc456 5

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,

Patrick

5 REPLIES 5
Community Champion

why for abc456 in table 2 returns 5, not 10 (newest in table 1 that older then abc456)?

do not hesitate to give a kudo to useful posts and mark solutions as solution

do not hesitate to give a kudo to useful posts and mark solutions as solution
Helper II
Ah you're right of course, just a typo on my end
Community Champion

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
Helper II

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.

Helper II

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.