Forum Discussion

vyacheslavg's avatar
vyacheslavg
Icon for Helper II rankHelper II
8 years ago
Solved

How 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...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    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.

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

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.

 

mbahonen's avatar
mbahonen
Icon for Advocate II rankAdvocate II
3 years ago

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's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    You are welcome.