Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a look up table that shows Items, a From and To date along with a price. This table will have the same Item name muliple times with From and To dates ending and starting after each other. (Never overlapping).
I then have a data table that has the item name (fish) and a date of purchase.
The outcome i am after is to return the price that was correct as at the time of purchase. An example (in excel) is shown below.
Because the item Name occurs multiple times in the Lookup table and in the Data table i can't build a relationship that works.
Any help would be appreciate (The values in green is what i am hoping to return)
Cheers Todd.
Solved! Go to Solution.
@ToddMate , A new column
New column in table 2 =
maxx(filter(Table1, Table1[Item Name] = table2[Item Name] && Table2[Date Purchase] >= Table1[from] && Table2[Date Purchase] <= Table1[to] ) Table1[Price])
@ToddMate , A new column
New column in table 2 =
maxx(filter(Table1, Table1[Item Name] = table2[Item Name] && Table2[Date Purchase] >= Table1[from] && Table2[Date Purchase] <= Table1[to] ) Table1[Price])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |