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.
Solved! Go to Solution.
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 :
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
Check out new user group experience and if you are a leader please create your group!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates