Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 So my normal go to for an alternative to LOOKUPVALUE is simply MAXX(FILTER(...),...)
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]))
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.
Thanks for the solution, it worked perfectly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
65 | |
50 | |
45 | |
20 | |
17 |