Forum Discussion

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
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
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