Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.
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
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.
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
72 | |
71 | |
70 | |
43 | |
42 |
User | Count |
---|---|
49 | |
42 | |
29 | |
28 | |
27 |