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.
Super UserHi,
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.
- vyacheslavg
Hi Ashish,
this is absolutely correct solution, it works perfectly.
Also this DAX is beoynd my current knowledge :catsad:, I need to improve it (knowledge) - so I will be able to solve similar tasks in the future.
Thanks!
- Ashish_Mathur
You are welcome.
Thanks Ashish Mathur - had a similar issue and this solved the problem perfectly.
- Ashish_Mathur
You are welcome.
mbahonenAdvocate II
I know this is very old, but I just wanted to say thank you so much! I have been working on a similar problem as the OP, and my solutions thus far were close, but I was missing the use of the EARLIER function. Many many thanks! This is great!
- Ashish_Mathur
You are welcome.
