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.
Please suggest some solution for desired results to work accurately.
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.
Please suggest some solution for desired results to work accurately.
Attachments
Last edited: