DAX for computation of Opening and Closing Balance of Inventory

[VKB]

Hello Experts,
Need help in fixing the DAX queries to compute Opening and Closing Balance and suggestions to improve other DAXmeasures.

While I was trying to make 2 tables (One on Monthly and the other on Daily status) on inventory movements, there are issues with the Opening and Closing Balances calculation.  link to the .pbix file has been given below. The objective is to show the stock quantity in the format of 'Item name' as rows and  |Opening Balance| Purchases | Production | Sales | Closing Balance| as columns. on the top of these columns, I need 'Months' in one table and 'Dates' in the other (The below pics are for your reference):

https://drive.google.com/file/d/1W2nO7Fd6JmQmgGg4Q5P2w0TdVjDWXm44/view?usp=share_link

Kindly refer to all other measures too and guide me wherever improvements can be done. 

Cheers!

Venkat

[VKB]

@v-cgao-msft Thank you for your kind responce. While  checking the output, The source data, Jan-22 does'nt have any Opening Balance and Feb-22 Opening balance shall be equal to Jan-22's Closing Balnace and likewise, the DAX should print the numbers for all months with similar logic.  hope this requirement make sense.

Cheers,
Venkat!

[v-cgao-msft · Accepted solution]

Hi @VKB ,

Modified the formula.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[VKB]

It's working,
Thank you!

[v-cgao-msft]

Hi @VKB ,

Please try:

Opening Stock 2 = 
VAR _mindate = FIRSTDATE( 'Stock Register'[Date] )
VAR _result = 
CALCULATE(
    SUM( 'Stock Register'[Quantity] ),
    'Date'[Date] <= _mindate
)
RETURN
_result

Closing Stock 2 = 
VAR _maxdate = LASTDATE( 'Stock Register'[Date] )
VAR _result = 
CALCULATE(
    SUM( 'Stock Register'[Quantity] ),
    'Date'[Date] <= _maxdate
)
RETURN
_result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum