Create an Inventory Value Over Time PivotChart in Excel with Power Pivot and DAX
Question details
The user needs to build a PivotChart that shows daily warehouse inventory value, compares months, and treats unsold items as current inventory using the Excel Data Model and DAX measures.
- Product
- Microsoft Excel Power Pivot and Power Query
- Device & OS
- not provided
- Scenario
- A warehouse or inventory analyst tracks delivered and sold quantities and costs daily and wants a dynamic PivotChart to compare monthly inventory value while unsold items continue to count through the current date.
- Observed behavior
- The PivotChart should update as source data grows and show Inventory Amount equal to Cost Delivered minus Cost Sold for each day or month.
Make sure your source table has separate columns for delivery date, delivered cost, sold date, sold cost, item ID, and cost details, and that you are using an Excel version that supports Power Query and the Data Model.
Create the PivotChart with Excel Power Query, Data Model, and DAX Measures
Use this solution when the source table will continue to grow and you need true daily inventory valuation with unsold items carried forward automatically.
WPS Office does not support the Excel Data Model or DAX measures, so this solution requires Microsoft Excel. In WPS Office you can approximate the result with a prepared inventory table, as shown in the next solution.
In Excel, go to Data > From Table/Range, transform the source table, and load it by selecting Only Create Connection and Add this data to the Data Model.
Open Power Pivot > Manage, create a calendar table with all dates from the earliest delivery date through the current date, and mark it as a date table.
In Power Pivot Diagram View, connect the calendar date to the delivery date and sold date fields through the appropriate item or lookup tables.
In Power Pivot, define Cost Delivered, Cost Sold, and Inventory Amount := [Cost Delivered] - [Cost Sold].
Go to Insert > PivotChart > Use an external data source > Choose Connection, select the Data Model, and place Date in Axis and Inventory Amount in Values.
Approximate the Same PivotChart in WPS Office with a Prepared Inventory Table
Use this approach if you work primarily in WPS Spreadsheets and can prepare daily inventory rows before creating a standard PivotChart.
Use WPS Office as a Free Alternative for Inventory PivotCharts and Daily Spreadsheet Work
WPS Office does not support the Excel Data Model or DAX measures, but it is a free, lightweight spreadsheet suite that opens .xlsx inventory files, preserves common formulas and PivotCharts, and provides a familiar ribbon interface for daily reporting.
- 1. Download WPS Office Free: Go to the official WPS Office website and click Download WPS Office Free.
- 2. Open your Excel inventory workbook: Launch WPS Spreadsheets and open the .xlsx file that contains your prepared inventory data.
- 3. Create a standard PivotChart: Select the prepared table, go to Insert > PivotChart, and place Date in Axis and Inventory Value in Values.
Frequently Asked Questions
Can I create the DAX Inventory Amount measure in WPS Office?
No. WPS Spreadsheets does not support the Excel Data Model or DAX measures. You need Microsoft Excel Power Pivot for DAX, or you can prepare an Inventory Value column in WPS and use a standard PivotChart.
How do I make unsold items count as inventory through the current date?
In the Excel Data Model, use a calendar table relationship and a measure that subtracts sold cost from delivered cost. In WPS, create a daily row for each unsold item and fill its inventory value through the current date before creating the PivotChart.
Will the PivotChart update automatically when source data grows?
In Excel with Power Query and the Data Model, yes, refresh the query connection to pull new rows. In WPS, update the prepared table first and then refresh the PivotChart.
Can WPS Office open an Excel workbook that already contains a Data Model PivotChart?
WPS Office can open .xlsx files, but Power Pivot and DAX objects may not work as expected. Keep a flattened table version of the data for WPS compatibility.
