Creating a Accumulated Net Inventory on hand qty table

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