Ending Inventory Calculation Help

[naoyixue1]

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

.....

[naoyixue1]

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 

Ending Inventory Balance (C) = CALCULATE('Measure Table'[Ending Inv],FILTER(ALL('Time Lookup'),'Time Lookup'[Mo Year]<=max('Time Lookup'[Mo Year])))

[naoyixue1]

@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..... 

[naoyixue1]

@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!

[mohammedadnant]

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