Forum Discussion
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 get calculated for each future month, based on the formula
NextMonthOpening Stock = PreviousMonthStock-PreviousMonthConsumption+PreviousMonthReceiving.
Please help on the expression how to get expeceted result.
I have attached a screenshot for better understanding.
you can try this
Column =VAR _STOCK=MAXX(FILTER('Table','Table'[Material]=EARLIER('Table'[Material])&¬(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)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
2 Replies
you can try this
Column =VAR _STOCK=MAXX(FILTER('Table','Table'[Material]=EARLIER('Table'[Material])&¬(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)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