Calculate Weekly Stock Opening and Closing Balances in Power Pivot
Question details
The user needs to calculate the weekly opening and closing stock balances filtered by country using Power Pivot DAX measures.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Managing inventory data where opening stock equals the previous week's closing stock, and closing stock is calculated based on opening stock, sales, intake, and markdowns.
- Observed behavior
- Initial attempts using calculated columns resulted in circular dependency errors or incorrect values because the calculations were not structured as properly sequenced DAX measures.
Ensure your raw data contains detailed transaction records (sales, intake, markdowns) with accurate dates and country identifiers before building your data model.
Build Sequential DAX Measures for Stock Balances
Avoid circular reference errors by calculating movements with individual DAX measures and referencing prior periods sequentially instead of using calculated columns.
When calculating inventory balances, using calculated columns often leads to circular dependencies because opening and closing balances inherently rely on each other. Instead, use DAX measures to dynamically calculate totals based on calendar weeks and dimensions.
Set up a dedicated Date or Calendar table in Power Pivot that includes a 'Week' dimension. Link this table to your main transaction data using the primary date field.
Create separate DAX measures for each movement type using the SUM function: Total Sales, Total Intake, and Total Markdowns.
Create an Opening Stock measure by retrieving the previous week's Closing Stock. Use the CALCULATE function combined with a filter that evaluates the prior week's data context.
Create a measure for Closing Stock by defining it logically as: [Opening Stock] - [Total Sales] + [Total Intake] - [Total Markdowns].

Manage Your Inventory Data Smoothly with WPS Office
If complex DAX modeling in Microsoft Excel is overwhelming, you can manage and calculate your weekly inventory stock balances using standard formulas and PivotTables in WPS Spreadsheet. It provides a lightweight, highly compatible, and user-friendly environment for your daily data analysis tasks.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your Excel workbook containing the raw inventory and transaction data.
- 2. Create a PivotTable: Select your dataset, go to the Insert tab, and click PivotTable to logically organize your sales and stock data by country.
- 3. Apply custom formulas: Use standard worksheet formulas alongside your PivotTable to calculate weekly opening and closing balances without needing complex DAX logic.

Frequently Asked Questions
Why do I get a circular dependency error when calculating opening and closing stock in Power Pivot?
Circular dependencies occur when you use calculated columns where Column A (Opening Stock) refers to Column B (Closing Stock), and Column B refers back to Column A. To fix this, build your model using dynamic DAX measures instead of calculated columns.
How can I reference the previous week's closing stock in DAX?
You can calculate the previous week's closing stock by using the CALCULATE function combined with a filter that subtracts 1 from the current week number, or by utilizing time intelligence functions like DATEADD depending on your calendar table structure.
What is the best way to validate DAX formulas for stock analysis?
Before writing complex DAX measures, create an expected-results worksheet using standard Excel formulas. Map out your opening stock, sales, intake, markdowns, and closing stock manually to ensure your business logic is fully correct, then translate it into DAX.




