1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Ask an Excel Question' started by Ramzan, Jun 13, 2018.

  1. Ramzan

    Ramzan New Member

    Messages:
    2
    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.

    Attached Files:

    Last edited: Jun 13, 2018
  2. GraH - Guido

    GraH - Guido Active Member

    Messages:
    671
    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?
  3. Ramzan

    Ramzan New Member

    Messages:
    2
    Show Items with no data on rows.

Share This Page