Creating a Accumulated Net Inventory on hand qty table

[ianburns143]

Hi all

Very new to coding in power query and just started using Power Pivot.. but getting there slowly, building myself up to get into Power Bi

basically i have 3 tables  which i need to transform

1/ STOCK onhand - Type = Stock, Code , QTY Transaction date = Today - per item could have various Qty in same day

2/ STOCK IN WARD -Type = Work order,  Code , QTY Transaction date =  various - per item could have various Qty's in same day

3/ STOCK OUT WARD Type = Sales order , Code, QTY , Transaction date = various - per item could have various Qty's in same day

Examples of my Tables (simplified)

TYPE	CODE	QTY	DATE
STOCK	A1	6	31/07/2025
STOCK	A1	4	31/07/2025
STOCK	C1	1	31/07/2025

TYPE	CODE	QTY	DATE
WORK ORDER	B1	20	31/07/2025
WORK ORDER	A1	20	02/08/2025
WORK ORDER	A1	10	03/08/2025
WORK ORDER	B1	2	28/08/2025
WORK ORDER	A1	10	05/09/2025
WORK ORDER	C1	10	05/09/2025

TYPE	CODE	QTY	DATE
SALES ORDER	A1	20	01/07/2025
SALES ORDER	A1	10	01/07/2025
SALES ORDER	A1	10	01/08/2025
SALES ORDER	B1	20	02/08/2025
SALES ORDER	C1	6	03/09/2025

The calculation = Stock qty per code + Work order per code - Sale order per code ( did do a power pivot measure with this calculation, but the qty's are not accumulated from 1 period (date) to the next )

so for any give date (time frame) I can create a  power pivot table to pull through as below

	July	August	Sept
CODE	NET STOCK	WORK ORDER	SALES ORDER	NET STOCK	WORK ORDER	SALES ORDER	NET STOCK	WORK ORDER	SALES ORDER	NET STOCK
A1	10	0	30	-20	30	10	-10	10	0	0
B1	0	20	20	0	2	0	2	0	0	2
C1	1	0	0	1	0	0	1	10	6	-5
D1	0	0	0	0	0	0	0	0	0	0

as an add on for STOCK table I have a created date = today. however Sales Order or Work Order Dates could be in the Past.. Is there a away If all these tables are joined that it will reflect the earliest date in the full data set.

Any help in how I pull these 3 tables together to get the desired output would be greatly appreciated , and which i will ofc added to my Power Query/ Power Pivot (Bi) knowledge 

[jaineshp]

Hey @ianburns143,

You need to create a running total inventory calculation that combines three transaction types and accumulates values over time. The formula is: Stock On Hand + Work Orders - Sales Orders = Net Inventory.

Step-by-Step Implementation

1. Data Preparation in Power Query

Combine All Transaction Tables:

• Append all three tables (Stock, Work Orders, Sales Orders) into a single transactions table
• Ensure consistent column structure across all tables
• Add a multiplier column: Stock (+1), Work Orders (+1), Sales Orders (-1)

Source Tables Structure:
- TYPE | CODE | QTY | DATE
- Add calculated column: [Multiplier] = if [TYPE] = "SALES ORDER" then -1 else 1
- Final QTY = [QTY] * [Multiplier]

2. Create Date Dimension Table

Establish Proper Date Relationships:

• Create a continuous date table covering your entire date range
• Set earliest date as minimum date from all three source tables
• Ensure proper date relationships for time intelligence functions

3. Power Pivot Measures Implementation

Base Inventory Measure:

Net Inventory =
SUMX(
ALL(Transactions),
Transactions[QTY] * Transactions[Multiplier]
)

Accumulated Inventory Measure:

Accumulated Net Inventory =
CALCULATE(
[Net Inventory],
FILTER(
ALL(Dates[Date]),
Dates[Date] <= MAX(Dates[Date])
)
)

4. Transaction Type Handling

Separate Calculations by Type:

• Stock On Hand: Filter TYPE = "STOCK"
• Inward Stock: Filter TYPE = "WORK ORDER"
• Outward Stock: Filter TYPE = "SALES ORDER"
• Apply appropriate positive/negative values for each type

5. Time-Based Accumulation Logic

Key Considerations:

• Stock entries use current date (TODAY)
• Work Orders and Sales Orders use historical transaction dates
• Running total must consider all transactions up to the selected date
• Multiple transactions per day per item must be properly aggregated

6. Data Model Relationships

Required Relationships:

• Transactions table linked to Date dimension on transaction date
• Item codes properly categorized for filtering
• Proper cardinality settings (many-to-one from transactions to dates)

7. Expected Output Structure

Final Pivot Table Format:

CODE | DATE | STOCK | WORK ORDER | SALES ORDER | NET STOCK | ACCUMULATED NET
A1 | 31/07| 10 | 0 | 0 | 10 | 10
A1 | 01/08| 0 | 0 | 100 | -100 | -90
A1 | 02/08| 0 | 200 | 0 | 200 | 110

8. Power Query Transformation Steps

Data Consolidation Process:

1. Load all three source tables
2. Standardize column names and data types
3. Add transaction type identifiers
4. Create unified transaction table
5. Establish proper date formatting
6. Add calculated columns for inventory direction

9. Validation and Testing

Quality Assurance Steps:

• Verify total quantities match source data
• Test accumulation logic with known scenarios
• Confirm date filtering works correctly
• Validate negative inventory scenarios
• Check multiple transactions per day handling

10. Performance Optimization

Efficiency Considerations:

• Use proper indexing on date and code columns
• Implement incremental refresh for large datasets
• Consider partitioning by date ranges
• Optimize DAX calculations for better performance

This solution will provide you with a robust inventory tracking system that accurately reflects accumulated net inventory positions over time, handling all your transaction types appropriately while maintaining data integrity and calculation accuracy.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer

[ianburns143]

Hi @jaineshpoojara 

Thank you so much for taking some time to show me this.  I have followed your steps but I am not seeing the desired output. looks like it is summing all the Qty's across the dataset ,

Power Pivot Table below , with settings and what the desired output should look like

Here are the Steps followed

Source Tables

1/ I have alligned all the Fields across all 3 tables to have matching attributes

2/ I have created the date table (date only) running 1st day of my dataset to the last date

3/ In all 3 tables I have grouped all the Qty's having the same dates

Transaction Table

1/ Append all 3 tables together -  output below 

2/ Created the Calculated Multiplier  and Final Qty columns as you requested

transaction table output

Power Pivot

1/Date table and transaction table loaded into Data Model and relationship established between dates

2/ I have created the following measures in the Transaction table

a)  Net Inventory:=SUMX(ALL(Transactions),Transactions[QTY] * Transactions[Multiplier])

b) Accumulated Net Inventory:=CALCULATE([Net Inventory],FILTER(ALL(Dates[Date]),Dates[Date] <= MAX(Dates[Date])))

c) STOCK:=SUMX(FILTER(Transactions,Transactions[TYPE] = "STOCK" ),Transactions[FINAL QTY])

d)WORK ORDER:=SUMX(FILTER(Transactions,Transactions[TYPE] = "WORK ORDER" ),Transactions[FINAL QTY])

e)SALES ORDER:=SUMX(FILTER(Transactions,Transactions[TYPE] = "SALES ORDER" ),Transactions[FINAL QTY])

I am probally missing something simple, but if you could advise it would be greatly appreciated

[jaineshp · Accepted solution]

Hey @ianburns143,

Looking at your issue, I can see the problem. Your measures are summing ALL transactions across the entire dataset instead of respecting the pivot table's row context (CODE and DATE). The ALL(Transactions) function is removing all filters, which is why you're getting totals across everything.

Here's the alternate approach:

Replace your current measures with these corrected versions:

1. Net Inventory (Fixed):

Net Inventory = SUM(Transactions[FINAL QTY])

2. Accumulated Net Inventory (Fixed):

Accumulated Net Inventory =
CALCULATE(
SUM(Transactions[FINAL QTY]),
FILTER(
ALL(Dates[Date]),
Dates[Date] <= MAX(Dates[Date])
)
)

3. Individual Transaction Types (Fixed):

STOCK =
CALCULATE(
SUM(Transactions[FINAL QTY]),
Transactions[TYPE] = "STOCK"
)

WORK ORDER =
CALCULATE(
SUM(Transactions[FINAL QTY]),
Transactions[TYPE] = "WORK ORDER"
)

SALES ORDER =
CALCULATE(
SUM(Transactions[FINAL QTY]),
Transactions[TYPE] = "SALES ORDER"
)

Why Your Original Measures Weren't Working

1. ALL(Transactions) removes all filters from the Transactions table, including the CODE filter from your pivot table rows
2. SUMX(ALL(Transactions), ...) was calculating the same total for every row
3. The measures weren't respecting the individual item codes (A1, B1, C1, etc.)

Key Changes Made

1. Removed ALL(Transactions) - Now measures respect the pivot table context
2. Used SUM() instead of SUMX() - More efficient for simple aggregations
3. Used CALCULATE() with filters - Properly filters by transaction type while maintaining row context
4. Kept ALL(Dates[Date]) only in accumulation - This is correct for time intelligence

Expected Behavior Now

• Each measure will now calculate only for the specific CODE in each row
• The accumulated measure will sum all transactions for that CODE up to the selected date
• Individual transaction type measures will show only their respective values

Try these corrected measures and your pivot table should now show the proper running totals by item code and date as desired.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer

[ianburns143]

@jaineshpoojara 

Hi jainesh, this is perfect thank you so much for the solution, but more importantly thank you for giving me an insight into the actual coding and why it wasn't working.... very insightful!

[jaineshp]

Hey @ianburns143,

Thank you for the kind recognition - always happy to contribute!

Fixed? ✓ Mark it as solution • Give Kuddos • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer

[ianburns143]

Sorry about the formating on the tables , didn't pull through as I expected , On the output should look like this