cancel
Showing results for
Search instead for
Did you mean:
Helper I

## 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.

1 ACCEPTED SOLUTION
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
5 REPLIES 5
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Helper I

Thank you so much for your help @Ashish_Mathur

Super User III

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User IV

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/

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helper I

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

## Helpful resources

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors