Closing Balance Calculation

[ChumaAmako]

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.

@Greg_Deckler @ImkeF  

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[ChumaAmako]

Thank you so much for your help @Ashish_Mathur 

[Ashish_Mathur]

You are welcome.

[amitchandak]

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/

[ChumaAmako]

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