cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChumaAmako
Helper I
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.

 

 
 

image.png

@Greg_Deckler @ImkeF  

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

Thank you so much for your help @Ashish_Mathur 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User IV
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!

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 

 

image.png

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors