Rolling Inventory

[kjkreik]

I have tried to get this to work and have searched all available resources to figure out how to transfer ending inventory to the next day's beginning inventory.  I have a table (Inventory) that is always "Today's" inventory.  I need to add production, subtract shipments to come up with ending inventroy for "Today", then roll today's ending to tomorrow's beginning etc. etc.  Easy in excel, thought it would be straightforward in PBI but for some reason I just havent been able to get it to work.  Help Please!

[Ashish_Mathur]

Hi,

I have solved a similar problem in the attached files.

Hope this helps.

[kjkreik]

Any reason day 0 closing inventory wont transfer to day +1 opening inventory?  Days after +1 all work correctly.  I double checked all DAX to make sure it matches your example2 file.

[Ashish_Mathur]

No idea.  If day 3 onwards are working fine, day 2 should work fine as well.  Recheck all the measures.

[kjkreik]

Here is your example2 file when I drop dim_date[cal_date].

[Ashish_Mathur]

Everything works just fine on my file.  I do not know the mistake that you are committing.

[amitchandak]

@kjkreik , Inventory need to be calculated in a cumulative manner

Beginning of Time(BOT) Inventory + Purchase till date from BOT - Sales till Date
Inventory / OnHand
[Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=Min(date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <=Min(date[date])))


Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) +
CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <min(date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <min(date[date])))

Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw