Forum Discussion

jitheshpc's avatar
jitheshpc
New Member
3 years ago

Inventory Projection

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?

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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