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

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

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

DAX

DAX Calculation

DAX Date condition

DAX Max Date within date range

Help required

POWER BI Dax rules

Anonymous

4 years ago

Hi Anonymous ,

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.