2

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.

Improve this question
2
  • 1
    Can you please edit your question, remove the rant and ask something specific?
    – teylyn
    Commented Oct 30, 2017 at 22:33
  • "Is there a specific setting or reason why I cannot group in my PivotTable, and can I adjust this setting so I can group in my PivotTable?" Specific enough?
    – Sultan of Swing
    Commented Oct 30, 2017 at 22:43

2 Answers 2

Reset to default
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.

enter image description here

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.

Improve this answer
2
  • The data is very simple, see my example in my question. No matter how I organize my PivotTable (whether Date is in rows, columns, filters, etc.) the group button is greyed out.
    – Sultan of Swing
    Commented Oct 31, 2017 at 16:33
  • Then it's not a groupable field. If it is a real date and sits in either row or column, then it can be grouped. If your experience is different, then there is a problem with the data. Post a sample file with as small data sample to a public file share for inspection and share a link here.
    – teylyn
    Commented Oct 31, 2017 at 22:47
0

I had the same issue even with a regular Excel table and a proper date field. After I had checked that every single entry in the date column was an actual date, and not a string, I made one little change, and grouping was enabled:

date formatting

I changed the date formatting in the original table from "adapting to locale" to "not adapting to locale". This did markedly not change the original column's property of being a proper date column, but it did change the pivot table's handling of the resulting field. Grouping was enabled as soon as I refreshed the pivot table with Alt+F5.

After that, surprisingly, I could change the formatting back to what it was before, and the pivot table could still handle it.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .