Return Latest-Date Inventory in an Excel Power PivotTable
Question details
Return the inventory value recorded on the latest available date, rather than returning the highest inventory number.
- Product
- Microsoft Excel Power Pivot and PivotTable
- Device & OS
- not provided
- Scenario
- The user is building a PivotTable from a Power Pivot data model and needs an inventory measure that follows the current report filters.
- Observed behavior
- Using MAX on the inventory field returns the largest inventory value instead of the inventory associated with the most recent date.
Confirm that the source table includes a valid Date column and an Inventory column, and that both are loaded into the Excel Data Model. Decide whether inventory values on the latest date should be summed, averaged, or otherwise aggregated when multiple records exist.
Create DAX measures for the latest date and its inventory value
Use this method when your PivotTable is based on the Excel Power Pivot Data Model and must remain reliable after Power Query refreshes or layout changes.
Create measures in the data model instead of referencing a fixed PivotTable cell. The first measure finds the latest date within the current filter context, while the second returns inventory only for that date.
The example below sums inventory when more than one source row exists for the latest date. Replace SUM with another aggregation if your data requires a different result.
In Excel, select the Power Pivot tab and click Manage to open the Data Model. If the tab is unavailable, enable the Microsoft Power Pivot for Excel COM Add-in from File > Options > Add-ins.
In the calculation area for your inventory table, create a measure such as: Latest Inventory Date := MAX(Inventory[Date]). Replace Inventory with the actual name of your data table.
Create a second measure such as: Inventory at Latest Date := VAR LatestDate = [Latest Inventory Date] RETURN CALCULATE(SUM(Inventory[Inventory]), REMOVEFILTERS(Inventory[Date]), Inventory[Date] = LatestDate). This retains filters such as product or warehouse while evaluating inventory on the identified latest date.
Return to the PivotTable Fields pane, expand the data table, and drag Inventory at Latest Date into the Values area. Use this measure rather than a worksheet formula that points to a PivotTable cell.
Use a date table when reporting periods require controlled date filtering
Use this approach when the model has a separate calendar table or when slicers and reporting periods should control which dates are considered.
Use WPS Office for Everyday Excel-Compatible Inventory Reports
Power Pivot Data Model measures are a Microsoft Excel-specific feature. For everyday inventory spreadsheets, PivotTables, formulas, and report sharing, WPS Office provides a free, lightweight alternative with a familiar spreadsheet interface and smooth migration from existing Excel files.
Frequently Asked Questions
Why does MAX return the wrong inventory value in my PivotTable?
MAX(Inventory[Inventory]) evaluates the inventory column and returns its highest numeric value. To return inventory for the latest date, first calculate MAX(Inventory[Date]) and then calculate inventory with the Date column filtered to that result.
Where do I create a Power Pivot measure in Excel?
Select Power Pivot > Manage, choose the table in the Data Model, and enter the DAX expression in the calculation area at the bottom of the table. The measure will then appear in the PivotTable Fields pane.
Will the latest-date inventory measure respect warehouse and product filters?
Yes. The measure can preserve filters for fields such as warehouse, product, or category while removing and replacing only the filter on Inventory[Date]. The latest date is therefore determined within the current PivotTable context.
What happens if there are multiple inventory records on the latest date?
The sample measure uses SUM(Inventory[Inventory]), so it adds all matching inventory records for that date. Change SUM to MAX, MIN, AVERAGE, or another appropriate aggregation if your source data has a different business rule.
