Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I would like the formula to look for the SKU per plant per month per year first in the table A, then the table B and if it cannot find it in either, it needs to look for the standard cost in the period that has the last update.
Eg. Taking SKU 85021 for plant abcd for the month of March 2020 (material year).
Image 1:
Material number | Plant | Std cost | Posting period | Material posting year |
000000000000085021 | abcd | 87.84 | 2 | 2020 |
000000000000085021 | abcd | 92.07 | 4 | 2020 |
000000000000085021 | abcd | 92.07 | 5 | 2020 |
000000000000085021 | abcd | 92.07 | 11 | 2020 |
000000000000085021 | abcd | 82.07 | 9 | 2020 |
000000000000085021 | abcd | 92.07 | 6 | 2020 |
000000000000085021 | abcd | 93.07 | 8 | 2020 |
000000000000085021 | abcd | 92.07 | 7 | 2020 |
000000000000085021 | abcd | 97.07 | 10 | 2020 |
000000000000085021 | abcd | 92.07 | 1 | 2020 |
000000000000085021 | abcd | 92.07 | 12 | 2020 |
Image 2:
Material number | Plant | Standard cost per unit | Std cost |
000000000000085021 | abcd | 95.55 | 95.55 |
I have created in the 3rd table value column as below :
here value column is blank for march as period 3 is not there in the table 1 or 2. In this case it should calulate the value for period 2 as that is the previous update in period column for both tables.
Please let me know if anyone of you have any solution for this.
Thanks in advance!
Solved! Go to Solution.
Hi @raya2015,
According to your description, it seems like you want to get the value of the last available date from another table based on the current date.
If that is the case, you can extract the current 'Material number' and date to looping other table records:
lastvalue =
VAR currNumber =
SELECTEDVALUE ( Table[Material number] )
VAR currDate =
MAX ( Table[Date] )
VAR _lastdate =
CALCULATE (
MAX ( Table2[Date] ),
FILTER (
ALLSELECTED ( Table2 ),
[Material number] = currNumber
&& [Date] <= currDate
)
)
RETURN
CALCULATE (
MAX ( Table2[Value] ),
FILTER (
ALLSELECTED ( Table2 ),
[Material number] = currNumber
&& [Date] = _lastdate
)
)
Regards,
Xiaoxin Sheng
Hi @raya2015,
According to your description, it seems like you want to get the value of the last available date from another table based on the current date.
If that is the case, you can extract the current 'Material number' and date to looping other table records:
lastvalue =
VAR currNumber =
SELECTEDVALUE ( Table[Material number] )
VAR currDate =
MAX ( Table[Date] )
VAR _lastdate =
CALCULATE (
MAX ( Table2[Date] ),
FILTER (
ALLSELECTED ( Table2 ),
[Material number] = currNumber
&& [Date] <= currDate
)
)
RETURN
CALCULATE (
MAX ( Table2[Value] ),
FILTER (
ALLSELECTED ( Table2 ),
[Material number] = currNumber
&& [Date] = _lastdate
)
)
Regards,
Xiaoxin Sheng
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |