Forum Discussion
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 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" (edited, correct color is "yellow", of course - sorry for initial mistake. This is another confirmation that humans do make mistakes in boring repetitive tasks...).
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.
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.
12 Replies
- Ashish_Mathur
Super User
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
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.
- 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.
- TomMartensHey,
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.
- vyacheslavg
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 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.
I have a data table(table1) that has post dates and another table(table2) that has date ranges and values(ChgPerUnit) that I need based on where the post date falls. There are also two other criteria, the cptcode and affiliatename, which are on both tables, that need to match with the date range to return a value(ChgPerUnit)
table1: Charges_2021
table2: UnitRate
Value needed from UnitRate = ChgPerUnit
I tried the calculate field but get this error
CALCULATE(MAX(UnitRate[ChgPerUnit]), Filter(All(UnitRate),
UnitRate[StartDate]>=Charges_2021[post date]
and UnitRate[EndDate]<=Charges_2021[post date]
and Charges_2021[affiliatename] = UnitRate[AffiliateName]
and Charges_2021[cptcode] = UnitRate[CPTCode]))- Ashish_Mathur
Hi,
Write this calculated column formula in the Charges_2021 table
=CALCULATE(MAX(UnitRate[ChgPerUnit]),Filter(UnitRate,UnitRate[StartDate]<=earlier(Charges_2021[post date])&&UnitRate[EndDate]>=earlier(Charges_2021[post date])&&UnitRate[AffiliateName]=earlier(Charges_2021[affiliatename])&&UnitRate[CPTCode]=earlier(Charges_2021[cptcode])))
