How to lookup value from another table in date range, using date?

[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" (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.

Excel file with data

[pmelvin001]

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])))

[Ashish_Mathur · Accepted solution]

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]

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.

[Anonymous]

Thanks Ashish Mathur - had a similar issue and this solved the problem perfectly.

[Ashish_Mathur]

You are welcome.

[vyacheslavg]

Hi Ashish, 

this is absolutely correct solution, it works perfectly.

Also this DAX is beoynd my current knowledge , I need to improve it (knowledge)  - so I will be able to solve similar tasks in the future.

Thanks!

[Ashish_Mathur]

You are welcome.

[TomMartens]

Hey,

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.

Excel file with data