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.
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]))
Super UserHi,
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])))