Forum Discussion
Helper IIHow to lookup value from another table in date range, using date?
Hi,
Try these calculated column formulas in the Products Table
date_component=CALCULATE(MAX(Components[date_component]),FILTER(Components,Components[component_id]=EARLIER([component_id])&&Components[date_component]<=EARLIER([date_product])))
Status=LOOKUPVALUE(Components[Status],[component_id],[component_id],Components[date_component],[date_component])
Hope this helps.
Helper IIHello,
Could you please help, cannot figure out a seeminlgy simple task.
The task is very easy for human to grasp.
In a simplest case, we have 2 tables with dates and statuses.
Table 1 - "component statuses"
Component status is changed on the date and valid until next change.
component_id date_component Status
1 | 6/16/2009 | green |
1 | 7/16/2011 | red |
1 | 5/14/2012 | green |
1 | 6/7/2014 | red |
1 | 12/20/2016 | yellow |
2 | 8/7/2014 | green |
2 | 11/20/2016 | blue |
Table "when component was added to product/assembly"
product_id date_product component_id
5 | 5/14/2015 | 1 |
5 | 6/12/2017 | 1 |
5 | 8/14/2017 | 2 |
10 | 1/14/2012 | 1 |
10 | 2/3/2017 | 2 |
The ask is to pickup/lookup the color(status) from 1st table and add it to the 2nd table.
Example for the 1st row in product table.
Date is "5/14/2015". Component 1 was "red" during that period/range (using date lookup in table "component statuses"), so we need to add a status "red".
2nd row.
Date is "6/12/2017". Component 1 was "green" during that period/range, so we need to add "green".
And so on and so forth until the end of table "products".
I tried to merge, concatenate in Power Query, even iterate - no any luck.
Any suggestion would be appreciated or hints how to do that in DAX or Power Query or combination of both.
Even some partial automation would be helpful.