March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi Guys,
I have a table like this, which have 2 key point (Week Number, and Material Number). My expected outcome is i want to have a differences qty of each week and material. The differences is coming from the RT Value.
i had tried some formula like this
Solved! Go to Solution.
Is the output you want?
The calculated column
Diff = var a=FILTER('Supply_Chain Inventory_Forecast',[Week Number]=EARLIER('Supply_Chain Inventory_Forecast'[Week Number])-1&&[Material Number]=EARLIER('Supply_Chain Inventory_Forecast'[Material Number]))
var b=MINX(FILTER(a,'Supply_Chain Inventory_Forecast'[ReceiptDate]=MINX(a,[ReceiptDate])),[RT])
return [RT]-b
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the help, the formula works and the output same with my expectation.
Regards,
Ilhamsyah
Thanks for the help, i try the formula and it works but it works on the grouped table for all the week.
How about if i have this table like this
i try to use your formula but it seems the difference it's not same as it have multiple weeks in the same material number and i have many different material number.
My expectation is it can show the difference for each of material number from previous week but specifically at the last date of that particular week (Date is from Receipt Date column)
In the result i could know if there is significant qty drop from each week, which material number that contribute the most to the reduction.
Need your help for this.
Thanks
Regards,
Ilhamsyah
Is the output you want?
The calculated column
Diff = var a=FILTER('Supply_Chain Inventory_Forecast',[Week Number]=EARLIER('Supply_Chain Inventory_Forecast'[Week Number])-1&&[Material Number]=EARLIER('Supply_Chain Inventory_Forecast'[Material Number]))
var b=MINX(FILTER(a,'Supply_Chain Inventory_Forecast'[ReceiptDate]=MINX(a,[ReceiptDate])),[RT])
return [RT]-b
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You can refer to the following calculated column
Diff = var a=MAXX(FILTER('Supply_Chain Inventory_Forecast',[Week Number]=EARLIER('Supply_Chain Inventory_Forecast'[Week Number])-1&&[Material Number]=EARLIER('Supply_Chain Inventory_Forecast'[Material Number])),[RT])
return IF(a<>BLANK(),[RT]-a)
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
87 | |
86 | |
75 | |
49 |
User | Count |
---|---|
164 | |
149 | |
101 | |
73 | |
56 |