Forum Discussion

Prasun's avatar
Prasun
New Member
2 years ago
Solved

Opening Stock calculation for future months for multiple materials in Power BI or POwer Query

Hii I want to do a Opening stock calculation for future months for multiple materials, I have Stock, Consumption and Receiving and Material columns , Now for each material, opening stock should ge...
Need Help
Tips and Tricks
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    Prasun 

    you can try this

     

    Column =
    VAR _STOCK=MAXX(FILTER('Table','Table'[Material]=EARLIER('Table'[Material])&&not(ISBLANK('Table'[Stock]))),'Table'[Stock])
    VAR _cons=sumx(FILTER('Table','Table'[Month]<=EARLIER('Table'[Month])&&'Table'[Material]=EARLIER('Table'[Material])),'Table'[Consumption])
    var _rec=sumx(FILTER('Table','Table'[Month]<=EARLIER('Table'[Month])&&'Table'[Material]=EARLIER('Table'[Material])),'Table'[Receiving])
    return if(_STOCK+_rec-_cons<0,0,_STOCK+_rec-_cons)
     
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ryan_mayu 

     

    Your solution is great. It worked like a charm! It would be more perfect if you could add 1 to the formula.

     

     

    Best Regards,
    Yulia Xu

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
2 years ago

Prasun 

you can try this

 

Column =
VAR _STOCK=MAXX(FILTER('Table','Table'[Material]=EARLIER('Table'[Material])&&not(ISBLANK('Table'[Stock]))),'Table'[Stock])
VAR _cons=sumx(FILTER('Table','Table'[Month]<=EARLIER('Table'[Month])&&'Table'[Material]=EARLIER('Table'[Material])),'Table'[Consumption])
var _rec=sumx(FILTER('Table','Table'[Month]<=EARLIER('Table'[Month])&&'Table'[Material]=EARLIER('Table'[Material])),'Table'[Receiving])
return if(_STOCK+_rec-_cons<0,0,_STOCK+_rec-_cons)
 
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi ryan_mayu 

     

    Your solution is great. It worked like a charm! It would be more perfect if you could add 1 to the formula.

     

     

    Best Regards,
    Yulia Xu