DAX for Calculating Opening and Closing Stock including Previous Period's Records.

Hy all
Greetings and thanks in advance.
I have a large data table in Data Model in excel 2016. This table is in SQL Server and is connected to Data Model in excel with Power Query. It has millions of rows. I have created pivot table report on it.
I need to calculate opening and closing stock for selected period (date, month or year). The sample of data is as given below. I am showing just a few columns and records in sample.


I have added 3 measures in the Pivot table fields.
1-   Movement=calculate(sum('Table'[Qty]), 'Table'[In/Out]="In") - calculate(sum('Table'[Qty]), 'Table'[In/Out]="Out")
It is working fine.
2-   Opening Stock = calculate ( [Movement], filter (all ('Table'[Date] ), 'Table'[Date]<max('Table'[Date] )))
3-   Closing Stock = calculate ( [Movement], filter (all ('Table'[Date] ), 'Table'[Date]<=max('Table'[Date] )))

There is a problem in the results of measure No. 2 and 3.
The result is ok if pivot table is viewed for all periods without filtering. But when I filter period (Date, Month or Year)  in pivot table, then result is not correct. It does not bring forward the closing stock of previous period.
Please suggest me some change in dax formula or some new formula for opening and closing stock calculation including previous period's data.
I will be very grateful for any help.
The Pivot table I created is as given below.
   
My Sample excel file with Data Table Sample and Pivot Table is attached here.
Calculating-Opening-and-Closing-Sto.xlsx
M RamzanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try adding three Calculated Columns using the formulas given below

1)
=IF('Table'[In/Out]="IN",'Table'[Qty],-'Table'[Qty])

Open in new window

And name this column as Qty(IN/OUT)

2)
=SUMX(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])),[Qty(IN/OUT)])

Open in new window

And name this column as Opening Stock Qty

3)
=SUMX(FILTER('Table','Table'[Date]<=EARLIER('Table'[Date])),[Qty(IN/OUT)])

Open in new window

And name this column as Closing Stock Qty

Now add these three fields to the existing Pivot Table and see if that's what you are trying to achieve.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Here is the file for your reference.
Calculating-Opening-and-Closing-Sto.xlsx
M RamzanAuthor Commented:
Dear Subodh Tiwari (Neeraj). Thank you very very much for giving time.
The Calculated columns and formulas for them suggested by you seem to work fine. But these are not filtering item. If data table has just one Item then result is accurate but If data table has more than one items then the result is not accurate. When data table has more than one item then stock calculation for filtered item is not accurate. I attach two excel files to brief it. 1 excel file has table with only 1 item i.e. Bread. its results are accurate. the second excel file has 2-items i.e. Bread and Rusk. Its result is not accurate.
Calculating-Opening-and-Closing-Sto.xlsx
Calculating-Opening-and-Closing-Sto.xlsx
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You should have also mocked up the desired output manually somewhere so it was easy to visualize the issue you are talking about.
M RamzanAuthor Commented:
The table image i posted in the post shows that it has more than one item and also the Pivot table image i posted shows that i am desiring the result for a selected/filtered item.
In the meanwhile I am sorry for not highlighting this requirement in the post.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hmm...
I guess you will need to overwrite the filter context so you will need maybe Calculate with AllExcept Filter.
I will try to derive something useful tomorrow as I am busy in something else.
M RamzanAuthor Commented:
Dear Subodh Tiwari (Neeraj), Thank you very much.
I also wanted to get the result just by adding the measure and i wanted to avoid to add calculated columns in the data table because adding columns increases the size of excel file.
The subject of my post "DAX for Calculating Opening and Closing Stock including Previous Period's Records" shows this.
M RamzanAuthor Commented:
I have got a solution from another forum on another website. I have got a dax formula with AllExcept Filter and used as a measure instead of adding calculated column in table in data model.
Thank you very much Dear Subodh Tiwari (Neeraj).
Your help helped me too much in getting the solution.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi Ramzan,

Sorry! I got stuck with something else so didn't get time, actually forgot to derive a solution based on AllExcept.
But you confirmed that my guess about AllExcept was right.
Glad your issue has been resolved.
M RamzanAuthor Commented:
I have got a solution from another forum on another website. I have got a dax formula with AllExcept Filter and used as a measure instead of adding calculated column in table in data model.
Here is the link of that post from which i Got the solution.
http://community.powerbi.com/t5/Desktop/DAX-to-Calculate-Opening-and-Closing-Stock-including-Previous/m-p/412066#M189043

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
M RamzanAuthor Commented:
I got the solution from another forum.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
powerBI

From novice to tech pro — start learning today.