Forum Discussion
vyacheslavg
Helper II
Helper IIHow to lookup value from another table in date range, using date?
Hello, 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...
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.
TomMartens
Super User
Super UserHey,
I'm wondering how you derive the status green for the 2nd row of the 2nd table. I guess I do not fully understand the business rule, but with my understanding I would have come up with yellow. Because the latest date from table 1 that is smaller or equal to the date from table 2 indicates a yellow status.
Regards
Tom
I'm wondering how you derive the status green for the 2nd row of the 2nd table. I guess I do not fully understand the business rule, but with my understanding I would have come up with yellow. Because the latest date from table 1 that is smaller or equal to the date from table 2 indicates a yellow status.
Regards
Tom
- vyacheslavg
Hi Tom, you're exactly right, it will be yellow, because it was yellow on the closest earliest date of status change.
I don't know what I was thinking or probably took it from some other example. Sorry about that.
