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('Ta
It is working fine.
2- Opening Stock = calculate ( [Movement], filter (all ('Table'[Date] ), 'Table'[Date]<max('Table'[
3- Closing Stock = calculate ( [Movement], filter (all ('Table'[Date] ), 'Table'[Date]<=max('Table'
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
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('Ta
It is working fine.
2- Opening Stock = calculate ( [Movement], filter (all ('Table'[Date] ), 'Table'[Date]<max('Table'[
3- Closing Stock = calculate ( [Movement], filter (all ('Table'[Date] ), 'Table'[Date]<=max('Table'
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
Here is the file for your reference.
Calculating-Opening-and-Closing-Sto.xlsx
Calculating-Opening-and-Closing-Sto.xlsx
ASKER
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
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
You should have also mocked up the desired output manually somewhere so it was easy to visualize the issue you are talking about.
ASKER
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.
In the meanwhile I am sorry for not highlighting this requirement in the post.
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.
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.
ASKER
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.
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.
ASKER
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.
Thank you very much Dear Subodh Tiwari (Neeraj).
Your help helped me too much in getting the solution.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I got the solution from another forum.
1)
Open in new window
And name this column as Qty(IN/OUT)2)
Open in new window
And name this column as Opening Stock Qty3)
Open in new window
And name this column as Closing Stock QtyNow add these three fields to the existing Pivot Table and see if that's what you are trying to achieve.