Pivot Table Issues

How to Build a Weekly Stock, Sales, and Intake Report in Power Pivot

Nimra Malik Oct 19, 2026 869 views

Question details

The user needs to create a dynamic Weekly Stock, Sales, and Intake (WSSI) model that combines actual and projected inventory figures.

How to Build a Weekly Stock, Sales, and Intake Report in Power Pivot or Power BI

Product: Power Pivot / Power BI
Device & OS: not provided
Scenario: Tracking and projecting weekly inventory, sales, and supply chain intake across different products and locations.
Observed behavior: A functioning data model that calculates closing stock dynamically based on previous week's opening stock, sales, and intake data using DAX measures.

Before you start

Ensure your raw data for sales, intake, stock balances, and product locations are organized into clearly structured, separate tabular formats before importing them into your data model.

Solution 1Recommended

Creating the WSSI Report in Power Pivot or Power BI

Organize your data into relational tables and use DAX measures to automatically calculate opening and closing stock.

A reliable WSSI model requires a robust star schema structure and DAX time intelligence functions to pass the closing stock balance of one week to the opening balance of the next week seamlessly.

Import Source Data

Import your separate tables (Calendar, Products, Locations, Sales, and Intake) into the Power Pivot Data Model in Excel or into Power BI Desktop.

Establish Relationships

In the diagram view, create relationships between your fact tables (Sales, Intake) and dimension tables (Calendar, Products) using shared keys such as Date, Product ID, and Location.

Define Scenarios

Include a Scenario column in your data that identifies whether the figures represent 'Actual' or 'Projected' data so the model knows which values to prioritize.

Write DAX Measures

Create measures that select actual sales/intake when available, otherwise falling back to projections. Then, calculate closing stock using the DAX logic: Closing Stock = Opening Stock - Sales + Intake.

Build the Visual

Insert a PivotTable from the Data Model in Excel, or a Matrix visual in Power BI, placing the Calendar Weeks on the rows or columns and your DAX measures in the values area.

Creating the WSSI Report in Power Pivot or Power BI

Dynamic Updates: Once configured, you only need to add new actuals or projections to your source tables and refresh the model to update the entire WSSI report dynamically.

Solution 2

Structuring Relational Source Data Properly

Setting up a proper data schema is a prerequisite for accurate weekly grouping and DAX calculations.

Free Microsoft Office alternative

Need a Lightweight Tool for Standard Pivot Tables? Try WPS Office

While advanced DAX modeling requires Microsoft's Power BI or Power Pivot add-in, WPS Office provides a robust, free spreadsheet tool for standard inventory tracking. You can build comprehensive WSSI reports using built-in PivotTables, SUMIFS, and VLOOKUP functions without heavy data models.

Fully compatible with Microsoft Excel (.xlsx) formats and standard formulas.Create powerful PivotTables to summarize weekly sales and intake data effortlessly.Lightweight software that runs smoothly on Windows, Mac, and Linux.Access to free built-in templates for inventory and supply chain management.

microsoft office alternative - wps office

Frequently Asked Questions

Can I build a WSSI report without Power Pivot?

Yes. You can use standard spreadsheet functions like SUMIFS, VLOOKUP, and standard PivotTables to track weekly stock. However, it requires more manual formula linking between weekly columns compared to using DAX measures.

Why is my closing stock not rolling over to the opening stock in Power BI?

This usually happens if your Calendar table is not properly related to your fact tables, or if your DAX measure lacks the correct time intelligence function (like DATEADD) to reference the prior week.

How do I handle weeks where there are no actual sales data?

In your DAX measure, use the IF or ISBLANK function to check for actual sales. If the actuals are blank, instruct the measure to pull from the 'Projected' sales data instead.

Excel & Data Problems