Help required-Value from another table on the bases effective date

[spandiar]

Hi,

I have 2 tables, they are both join with product( many to many relationship).

Order
Order No	Orderdate	Product
1	01/01/2021	A
2	02/01/2021	B
3	01/02/2021	A
4	02/02/2021	B
5	02/02/2021	A
6	03/02/2021	B
7	25/02/2021	A
8	26/02/2021	A
9	07/02/2021	B
10	08/03/2021	A

Cost
Cost %	EffectiveDate	Product
1.1	01/01/2021	A
1.4	01/01/2021	B
1.5	01/02/2021	A
1.8	15/02/2021	A
1.9	01/03/2021	A
2	02/02/2021	B

Need the result like this 

Order
Order No	Orderdate	Product	Cost%
1	01/01/2021	A	1.1
2	02/01/2021	B	1.4
3	01/02/2021	A	1.1
4	02/02/2021	B	2
5	02/02/2021	A	1.5
6	03/02/2021	B	2
7	25/02/2021	A	1.8
8	26/02/2021	A	1.8
9	07/02/2021	B	1.5
10	08/03/2021	A	1.9

If write custom colum to get cost% in order table this its give error of multiple values

VCost% =LOOKUPVALUE('Product'[Cost%],'Product'[Product],'Order'[Product])

Or 
no result
Vcost% =CALCULATE(SELECTEDVALUE(Product[Cost%]),Orderdate>=MINX(Product,Product[EfffectiveDate])&& OrderDate <=MAXX(Product,Product[EffectiveDate]))

 OR

 I have tried this also but no result

Cost1% =

Var MinEffDate = CALCULATE(MINX('Cost','Cost'[EffectiveDate]),FILTER('Cost','Cost'[Product]=('Order'[Product])))

Var MaxEffDate = CALCULATE(MAXX('Cost','Cost'[EffectiveDate]),FILTER('Cost','Cost'[Product]=('Order'[Product])))

Return

CALCULATE(SELECTEDVALUE('Cost'[Cost%]),'Order'[Orderdate]>=MinEffDate &&'Order'[Orderdate] <=MaxEffDate,FILTER('Cost','Cost'[Product]=('Order'[Product])))

Can you please how can I achieve this result.
Thanks

[Greg_Deckler]

@spandiar So my normal go to for an alternative to LOOKUPVALUE is simply MAXX(FILTER(...),...)

[spandiar]

If I write following code then its gives circular dependency error 
Vcost% =CALCULATE(SELECTEDVALUE(Product[Cost%]),Orderdate>=MINX(Product,Product[EfffectiveDate])&& OrderDate <=MAXX(Product,Product[EffectiveDate]))

[v-rzhou-msft · Accepted solution]

Hi @spandiar ,

Try this code to create a calcualted column.

Cost % = 
VAR _MAXDATE_BEFORE =
    CALCULATE (
        MAX ( Cost[EffectiveDate] ),
        FILTER (
            Cost,
            AND (
                Cost[Product] = EARLIER ( 'Order'[Product ] ),
                Cost[EffectiveDate] <= EARLIER ( 'Order'[Orderdate] )
            )
        )
    )
VAR _COST =
    CALCULATE (
        SUM ( Cost[Cost %] ),
        FILTER (
            Cost,
            AND (
                Cost[Product] = EARLIER ( 'Order'[Product ] ),
                Cost[EffectiveDate] = _MAXDATE_BEFORE
            )
        )
    )
RETURN
    _COST

Result is as below.

You can download my sample file as below to learn more details.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[spandiar]

Thanks for the solution, it worked perfectly.