Fix Power Pivot Values Not Filtering by Selected Months in WPS Office Spreadsheets
Question details
The user needs a Power Pivot report to display working-day values from another table only for the months selected in a filter, instead of showing all months.
- Product
- Microsoft Excel Power Pivot
- Device & OS
- not provided
- Scenario
- The user is working with a Power Pivot data model that includes multiple fact tables and a month filter, and wants the report to respect the selected months.
- Observed behavior
- The report continues displaying working-day values for all months rather than only the selected months.
Before you start, open the Power Pivot Data Model or relationship view and identify the month/date columns in each fact table. Confirm that the month slicer or report filter can be sourced from a shared date table rather than from a single fact table.
Create a Shared Date Table and Use Filter-Aware Measures
Use this when a Power Pivot report ignores a month filter because the fact tables are not all related to the same date dimension.
The month filter must flow from a shared date or month table to every fact table. If the slicer uses a month column from one fact table, the other tables are not filtered, so the report shows all months.
In the Power Pivot Data Model, create a separate date table with continuous month columns, or import a calendar table that has one row per month.
Use Diagram View to relate each fact table's date or month key to the matching key in the shared date table, and verify each relationship is active.
Place the month field from the shared date table into the report filter or slicer, not from individual fact tables.
Update the working-day measure to use SUMX over the selected months, for example SUMX(VALUES('Date'[Month]), [WorkingDays]).
Refresh the report, select one or more months, and confirm the values change to match only the selected months.
Verify Relationships and Filter Direction in the Data Model
Use this if the report still ignores the month filter after adding a shared date table, as inactive relationships or incorrect filter direction can block filtering.
Use WPS Office Spreadsheets as a Free Alternative for Power Pivot Month Filters
Power Pivot is a Microsoft Excel add-in, so its data model relationship and filter behavior is managed inside Excel. WPS Office Spreadsheets is a free, lightweight Excel alternative that opens and saves .xlsx files with a familiar interface, making it easy to continue building PivotTable month views without paying for Office.
- 1. Download WPS Office Free: Go to the official WPS website and download the free WPS Office installer for your PC.
- 2. Open your Excel workbook in WPS Spreadsheets: Launch WPS Office, open the Excel file that contains the Power Pivot report, and let WPS preserve the .xlsx format.
- 3. Rebuild the month view with a WPS PivotTable: Use Insert > PivotTable and place the month field from a shared date table into the filter area to control the displayed months.
Frequently Asked Questions
Why does my Power Pivot report show all months even after I select a month filter?
Usually the month filter comes from a column in one fact table instead of a shared date table. Create a dedicated date/month table, relate all fact tables to it, and use that table's month field in the slicer or filter.
Do I need a separate date table for month filtering in Power Pivot?
Yes, a shared date or month table is the reliable way. Relate each fact table to the date table so the filter context can reach all tables.
Which DAX measure should I use to respect selected months?
Use filter-aware measures such as SUMX(VALUES('Date'[Month]), [WorkingDays]) so the calculation only iterates months currently selected in the filter context.
Can I fix month filtering without changing relationships?
You can try applying the filter from the fact table's own month column, but if multiple fact tables are involved, the report may still show all rows. A shared date table is more reliable.
Does WPS Office include Power Pivot?
Power Pivot is a Microsoft Excel add-in. WPS Office Spreadsheets includes built-in PivotTable tools and opens Excel workbooks, but for Power Pivot data model management you may need Excel; WPS is a free alternative for many report tasks.
