Inventory Projection

[jitheshpc]

Hi,  I have a peculiar problem, in my org, we use previous  months stock to project the coming months inventory the fomula is this :   PREVIOUS  MONTH'S STOCK - SALES FORECAST + INCOMING STOCK.  As shown below

The problem is eg : I have Feburary actual stocks in, with which I can calculate the March projected stock but, to calculate April, we dont have actual stock in March.  In excel it is easy,  how to I replicat this is Power BI DAX?

[amitchandak]

@jitheshpc , You need to think cumulative ways

examples

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: https://youtu.be/nKbJ9Cpb-Aw