Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. 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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
83 | |
75 | |
64 | |
39 | |
34 |
User | Count |
---|---|
107 | |
56 | |
52 | |
48 | |
40 |