Pivot Table Issues

How to Group Dates by Week in an Excel Power Pivot Timeline

Nimra Malik Oct 23, 2026 868 views

Question details

The user wants to group dates by week within an Excel Power Pivot timeline, but the default options only allow Days, Months, Quarters, and Years without an adjustable day count.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to analyze and filter data on a weekly basis using a timeline connected to a Power Pivot data model.
Observed behavior: Power Pivot timelines lack a direct 'Weeks' grouping option, and the native 'Days' option cannot be customized to 7-day increments, preventing straightforward weekly filtering.

Before you start

Ensure the Power Pivot add-in is enabled in Excel and that your source data contains a continuous, properly formatted date column with no blank values to allow for accurate relationship mapping.

Solution 1Recommended

Create a Calendar Table Using DAX for Weekly Grouping

The most reliable method to achieve weekly grouping in a Data Model is to build a separate Calendar table using DAX, generate week-based columns, and connect it to your main data.

Because native Timelines cannot slide by week, you must generate a Date dimension table containing calculated fields for the week number or week start date. You can then use these custom fields in standard Slicers to filter your Power Pivot data.

Open the Power Pivot Window

Navigate to the 'Power Pivot' tab on the Excel ribbon and click 'Manage' to open the Data Model.

Generate a New Date Table

In the Power Pivot window, go to the 'Design' tab, click 'Date Table', and select 'New'. This automatically generates a calendar covering the date range of your data.

Add a Calculated Week Column

Scroll to the first empty column on the right, click the header, and enter a DAX formula like '=WEEKNUM([Date])' to extract the week number, or use a custom formula to establish the week's starting date.

Establish a Relationship

Switch to 'Diagram View' (top right), and drag the 'Date' column from your new Calendar table to the date column in your original data table to create a one-to-many relationship.

Insert a Slicer for Weeks

Return to Excel, click on your PivotTable, go to 'PivotTable Analyze', and select 'Insert Slicer'. Choose your newly created Week column from the Calendar table to filter your data by weeks.

Create a Calendar Table Using DAX for Weekly Grouping

Tip: If you need the Slicer to display readable formats (like 'Week of Jan 1'), you can format the calculated DAX column as 'Year-Week' or a formatted string before inserting the Slicer.

Solution 2

Build a Date Table with Power Query

If you prefer working outside of DAX, you can use Power Query to generate a comprehensive date table that includes weekly identifiers before loading it into the Data Model.

Skip the Complex Data Models

Easily Group Dates by Week in WPS Spreadsheet

Instead of struggling with DAX formulas and Power Pivot relationships just to see weekly data, you can effortlessly group dates into 7-day intervals using standard Pivot Tables in WPS Office. It is built-in, fast, and requires zero coding.

1. Open Your Dataset: Launch WPS Spreadsheet and open the workbook containing your raw data.
2. Insert a PivotTable: Highlight your data range, go to the 'Insert' tab, and click 'PivotTable' to create a new pivot report.
3. Set Up the PivotTable: In the PivotTable Fields pane, drag your Date field into the 'Rows' area and your metric (e.g., Sales) into the 'Values' area.
4. Open Grouping Options: Right-click any date cell inside the generated PivotTable and select 'Group' from the context menu.
5. Group by 7 Days: In the Grouping dialog, ensure only 'Days' is highlighted (unselect Months or Years). Change the 'Number of days' field at the bottom to 7, and click OK to instantly view your data by weeks.

No need to write complex DAX or M queries for basic weekly grouping.Native support for custom day intervals (like 7 days) in standard Pivot Tables.Fully compatible with Microsoft Excel (.xlsx) file formats and existing datasets.Lightweight software footprint with fast processing for large data sets.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the Excel Timeline have a 'Weeks' option?

Excel Timelines are hardcoded to display Days, Months, Quarters, and Years. Because weekly intervals do not perfectly align with months or years (e.g., a week can span two months), the Timeline tool cannot natively handle a weeks view. Custom tables are required to bypass this.

Can I group regular Pivot Table dates by week without Power Pivot?

Yes. If you are using a standard Pivot Table (not added to the Data Model), you can right-click a date cell, select 'Group', highlight only 'Days', and set the number of days to 7. This avoids the need for a Data Model entirely.

What happens to my Timeline if I use a DAX calendar table?

While the native Timeline tool still won't show a 'Weeks' slider, you can use standard Slicers linked to your DAX 'Week Number' or 'Week Start Date' columns. This achieves the exact same interactive weekly filtering functionality on your dashboard.

Excel & Data Problems