Cannot group field in Excel PivotTable

Question

I know this question has been asked before, and the usual answer is "your dates are not all stored as dates!" I highly doubt this is the case here.

I'm using PowerPivot which pulls data from a database/table. I use a SQL query in PowerPivot to pull this data. Dates are stored as numbers, so January 1, 2017 is stored as 20170101. I just use a DAX formula to convert this to an actual date.

I've inserted a PivotTable to summarize my PowerPivot data. All I've done so far is put "Date" into rows and it lists all the dates from January till today. There are no blanks, no error values, no text values, nothing. I am able to put a timeline slicer in and it works (timelines only work when the data is formatted as a date correctly if I'm not mistaken).

For whatever reason, I cannot group the date into weeks. My end goal is to list top 5 selling products for each week, not day. By selecting "Last Week" in the date filters and then "Top 5" in the sales column, I'm getting the top 5 products for each day from last week.

I've tried being sneaky and finding the week number based on the date in PowerPivot directly. So I associate Jan. 1 2017 with week "01" in PowerPivot. Then I add this "week" column as a row in the PivotTable. Then I select the week column in the PivotTable and do "Top 10" and select the top 1 week, which will be the latest week (say, week 30). The problem is that I want to talk about the previous week. Right now, maybe I'm in week 30, but I want my "Top 10" filter for week to return the 2nd highest week and only the 2nd highest.

I'm already using index and match to lookup values and I don't want to have to add a new criteria.

Anyways, why can't I group in the PivotTable? Would there be an easier way to find the top products for the previous week, broken down by week and not day?

Edit as per user teylyn since my question is not obvious enough: Is there a specific setting I have missed that would allow me to group? Is there any other way to go about handling this problem?

The PowerPivot table looks like:

Date ID     Company     Product        Sales     Date
20170101    Joe's Shop  Joe's Product    $50     January 1, 2017, 12:00 AM

etc...

No matter how I organize the PivotTable, the Group buttons (either right clicked or under the Analyze menu) are both greyed out.

Answer 1

Pivot table date fields can be grouped by week, there just is not a single selector for it. You need to group by day, set a start date and the number of days to 7.

After that you can use a slicer to show only particular weeks.

Without seeing how your data or pivot table is structured it is not possible to tell how to show the top x performing products for that week. If you require more detail on that, edit your question and provide a data sample.