How to Group PivotTable Dates by 7-Day Periods with Distinct Count
Question details
The user needs to group dates by custom seven-day periods (weeks) in a PivotTable while keeping the Distinct Count feature active.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Creating a PivotTable and chart based on booking numbers across dates, requiring a distinct count of bookings grouped by custom weeks, but finding that standard date grouping is unavailable when the Data Model is used.
- Observed behavior
- When adding data to the Data Model to use Distinct Count, the standard PivotTable date grouping function becomes disabled, preventing automatic seven-day grouping.
Ensure that your source dataset contains a cleanly formatted date column with no blank cells or text strings, and map out exactly how you want your 7-day periods to be defined.
Use a Calendar Lookup Table in the Data Model
Create a separate calendar table defining the week number for each date, and link it to your source data to preserve Distinct Count.
When data is added to the Data Model to enable the Distinct Count feature, standard PivotTable date grouping is disabled. To bypass this limitation, you can build a custom calendar table that maps every possible date to your specific 7-day period and link the tables together.
Open a new worksheet and create a two-column list. In the first column, list every consecutive date covering your dataset. In the second column, enter the corresponding 7-day period or week number for each date.
Select your main booking dataset and press Ctrl+T to format it as a Table. Repeat this process for the new Calendar list you just created.
Go to the Data tab, click on the Data Model or Power Pivot window, and add both tables. Create a relationship by linking the Date column in your main dataset to the Date column in the Calendar table.
Insert a PivotTable using the Data Model. Drag the 'Week Period' field from your Calendar table into the Rows area, and drag your Booking Numbers into the Values area.
Right-click the values field, select 'Value Field Settings', scroll to the bottom of the calculation list, and select 'Distinct Count'.

Add a Helper Column Using VLOOKUP or Formulas
If you prefer not to manage Data Model relationships, use a helper column to merge the week periods directly into your source dataset.
Easily Analyze and Group Data with WPS Spreadsheet
WPS Spreadsheet offers intuitive PivotTable functionalities and powerful formula integration that makes summarizing complex datasets highly efficient.
- 1. Open Your Dataset: Launch WPS Spreadsheet and open the file containing your booking records and dates.
- 2. Set Up Your Grouping Formula: Add a helper column and use the WEEKNUM function to assign a specific 7-day period to each date row.
- 3. Insert the PivotTable: Highlight your data range, navigate to the Insert tab, and select PivotTable to begin your analysis.
- 4. Summarize Your Data: Drag the new week column into the Rows field and configure your desired booking metrics in the Values area using Value Field Settings.

Frequently Asked Questions
Why is the 'Group Dates' option grayed out in my PivotTable?
The 'Group' feature is automatically disabled if your PivotTable relies on the Data Model (which is required to enable Distinct Count). It can also be disabled if your date column contains blank cells or text formatted as strings instead of valid date values.
What is the difference between Count and Distinct Count in PivotTables?
'Count' tallies every single time an item appears in your data, including duplicates. 'Distinct Count' only counts unique instances of an item. For example, if a booking ID appears three times on the same date, Count returns 3, while Distinct Count returns 1.
Can I define a 7-day period starting on Wednesday instead of Monday?
Yes. If you are using the WEEKNUM function, you can adjust the return_type argument to change the start day. Alternatively, if you build a custom lookup calendar table, you can map any specific 7-day block to your desired week label.
Do I have to use the Data Model to calculate unique items?
Yes, standard PivotTables do not have the Distinct Count option natively. Checking 'Add this data to the Data Model' unlocks it. If you cannot use the Data Model, you must use complex array formulas or a helper column with formulas like =1/COUNTIF(range, criteria) to approximate distinct counts.




