Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Inventory calculation using DAX

Hi All,   I want to calculate the Closing/Opening inventory using DAX.    What I have: Brand |Jan Inv a |400 b |750 c |400   Brand |Month |Sales a |Oct-20 |900 a |Nov-20 |500 a |Dec-20 |...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want.

    Best Regards

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , You need to create a common date table and have month year there

 

Assuming only have inventory for Jan

 

Opening = CALCUALTE(Sum(Inventory[Inv]),all(Date))

Cumm Sales = CALCULATE(SUM(Sales[Sales]),filter(allselected(date),date[date] <=max(date[date])))

closing Inv = [Opening] -[Cumm Sales]
New opening = [Opening] - CALCULATE(SUM(Sales[Sales]),filter(allselected(date),date[date] <=maxX(Date,dateadd(date[date],-,month))))