Starting & Ending Inventory Columns

[dswinden]

Hello,  

Struggling with what feels like a very simple function.  My data looks like the below table example.  Essentially i need to create a starting inventory column and an ending inventory column.  Where;

Starting Inventory = Ending Inventory of Previous Week + New inventory

Ending Inventory = Starting Inventory of Current Week less Sales for that week

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Ensure there is a Calendar table with a Week starting column.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Ensure there is a Calendar table with a Week starting column.

[dswinden · Accepted solution]

This article is the key to the solution;

https://community.fabric.microsoft.com/t5/Desktop/Create-opening-and-closing-stock-for-each-month-fo...

[amitchandak]

@dswinden , You have to use a cumulative approach

Inventory / OnHand
[Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

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

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

[dswinden]

Hi @amitchandak as always, appreciate you are usually one of the first to reply!

WOuld you mind editing that DAX for the fields in my table above?  Thank you!