Forum Discussion
Closing Balance Calculation
Hi Everyone,
I am trying to compute the Stock at hand on any date or month etc.
The Stock on Hand (SOH) is calculated as = Opening Balance + Shipment - Sales
"Opening Balance" refers to the closing balance of the previous month or time period (Date, quarter, year etc)
I have 2 Tables (Sales & Shipment) which I pull data from, I also have a date table.
Please see below data structure of the tables and the desired outcome I need
I can do this quite easily in Excel, but can not seem to replicate in PowerBI, any help is greatly appreciated.
5 Replies
Create a common date calendar and join both tables. creating an opening balance like excel is a choice. So you have to start from the beginning. So cumulative measure will help
Cumm Shipment = CALCULATE(SUM(Shipment [Shipment]),filter(date,date[date] <=maxx(date,date[date]))) Cumm Sales = CALCULATE(SUM(Sales [Sales]),filter(date,date[date] <=maxx(date,date[date]))) stock = [Cumm Shipment] -[Cumm Sales]Have a month-year in your calendar table. That will allow you to stock at the month level
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Hi amitchandak
Thanks for your help, but this did not solve what I what, as it does not take into account the closing balance of the previous month
See below output. As you can see Feb-20 does not take into account the previous closing balance of 300.
Please click HERE to access the trial file if it would help
Thank you so much for your help Ashish_Mathur
You are welcome.
