• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dax. Problem in Calculating Opening and Closing Stock for Inventory Report

Status
Not open for further replies.

Ramzan

New Member
I have created pivot table report using Microsoft Power Query and Data Model. My source data table is in SQL Server and is connected to Data Model in excel with Power Query. It has millions of rows.
In my original file the data source is SQL Server but I have created excel tables for data source in sample file so that it can be refreshed if any modification is needed in Data Model or measures. I have minimized the data in sample file by deleting rows from excel table to minimize the file size.
I need to calculate opening and closing stock for items for the selected period (date, month or year).
Pivot Table A.
I have added three measures in it.
1- Change =
CALCULATE(sum(Data[_Qty]),Data[inv]="i")
-CALCULATE(sum(Data[_Qty]),Data[inv]="o")

2- Opening Balance =
CALCULATE ( [Change],
FILTER (
ALLEXCEPT ( Data, Data[Item Code], Data[Item Name], Data[Location]),
Data[Date] <min ( Data[Date])))

3- Closing Balance =
CALCULATE ( [Change],
FILTER (
ALLEXCEPT ( Data, Data[Item Code], Data[Item Name], Data[Location]),
Data[Date] <=min ( Data[Date])))
Problem:
The line for the Items is not shown for which there is no activity during the selected period (date, month or year). Activity means Inventory In (Purchase) and Inventory Out (Consumption).
Requirement:
The line for the Items should be shown for which there is no activity during the selected period (date, month or year) showing just opening and closing balance because opening and closing balance is needed for those items.

For solution I worked with another scenario. Which is Pivot Table B Sheet in the sample file.
Pivot Table B.
1- I have used multi table model. I created an extra table in data model for items list with the name Items.

2- I have added a custom column in Data table with the name Qty in Power Query with formula
Qty = [_Qty] * (if [inv] = "O" then -1.0 else 1.0)
3- I have Created relationships between tables in data model.
I have added two measures.
1- Opening Balance B =
IF ( HASONEVALUE (Items[Item Name] ), VAR vItem = VALUES (Items[Item Name] ) VAR vLastDate = MAX ( 'Calendar'[Date] ) RETURN CALCULATE( SUM ( Data[Qty] ), 'Calendar'[Date] < vLastDate ))

2- Closing Balance B =
IF ( HASONEVALUE (Items[Item Name] ), VAR vItem = VALUES (Items[Item Name] ) VAR vLastDate = MAX ( 'Calendar'[Date] ) RETURN CALCULATE( SUM ( Data[Qty] ), 'Calendar'[Date] <= vLastDate ))
Problem:
This Pivot Table is ok in showing opening and closing balances for Items with no activity. But it is not calculating Opening and Closing Balance Correct if more than one dates are selected or a whole month with all dates is selected.
Requirements:
1- Data should be shown even if there is no activity. (It is fine with Pivot Table B)
2- If more than 1 date is selected or a whole month or more than a month is selected, then opening and closing balance should be calculated accurately.

Summary of Results Requirements and Results for Pivot Tables is given in the table below.
upload_2018-6-13_13-9-27.jpeg
Please suggest some solution for desired results to work accurately.
 

Attachments

  • Inventory Report.xlsx
    1,013.3 KB · Views: 18
  • upload_2018-6-13_13-5-51.jpeg
    upload_2018-6-13_13-5-51.jpeg
    39.2 KB · Views: 9
Last edited:
I don't have access to the same version of PowerPivot for the moment and you are using functions that are not available in my version. Thus I'm blind for both your data model and used measures.
But in my version I could check these options in the pivot table options.
upload_2018-6-13_11-3-59.png
Does that change anything at your end already?
 
Status
Not open for further replies.
Back
Top