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.

 

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

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

    You are welcome.