Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hey all,
I tried to calculate the ending Inventory by each month. It should be last month ending inventory substracts the demands. See below . Do you know what kind of logic I should apply to get that results? Thanks!
2022 Jun = 6174 - 600 = 5574
2022 Jul = 5574 -1080 = 4494
2022 Aug = 4494 -1096 = 3398
.....
Hey all,
That's my thoughts, but some SKU ending inventory is wrong, that's why i asked the question on this Wed.
See below steps (Some SKUs results are correct, but some are not)
1: Calculate inventory balance = beginning inv + On Q + On H + WO
2: Ending Inv = last month inv + inventory balance
3: Cumulative ending inv
@mohammedadnant That one doesn't work. It will overconsumed the demands. For example, we already substracted the 1st month demands, but if you use the running total. It means in the 2nd month, we will consumed both 1st month and 2nd month demands. It overconsumed. Sorry....It doesn't work
MY thoughts is that we should calculate each month inventory balance but add the last month ending inventory.....
@mohammedadnant Thanks! Let me try today and to see whether it works...So just curious whether it will work for each month. You know I actually calculated the inventory balance firstly ( On hand invnetory - 1st month demand), and then applied the runinng total ,but it doesn't work for me. let me try you appraoch and let you know. Thanks!
Hi @naoyixue1
Try this,
Create 3 measures as follows
Runn AOH =
CALCULATE( SUM(InventoryTable[Available on Hand]),
ALL(InventoryTable[Mo Year]), InventoryTable[Mo Year]<=MAX(InventoryTable[Mo Year])
)
Runn Demand =
CALCULATE( SUM(InventoryTable[Demand]),
ALL(InventoryTable[Mo Year]), InventoryTable[Mo Year]<=MAX(InventoryTable[Mo Year])
)
Inventory Balance =
[Runn AOH]-[Runn Demand]
Here is the output
Thanks & Regards,
Mohammed Adnan
Learn Power BI: https://www.youtube.com/c/taik18
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |