Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
patrick3
Helper II
Helper II

Return value based on Date / Key pair

Hi all,

 

I've got 2 tables:

 

DateUnique KeyValue
01/01/2019abc1235
01/02/2019abc12310
01/01/2019abc4565
01/02/2019abc45610

 

DateUnique KeyValue
05/01/2019abc1235
05/02/2019abc12310
10/02/2019abc12310
15/02/2019abc4565

 

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
az38
Community Champion
Community Champion

hi @patrick3 

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
LinkedIn

Ah you're right of course, just a typo on my end
az38
Community Champion
Community Champion

@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


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

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.