Grouping by date disabled in pivot table

[sharshra]

I have a pivot table where I'm trying to group by week. But dates are disabled in grouping.

Tried all options suggested for similar issue in this forum & elsewhere in the internet. All dates are in date format, but still dates are disabled in grouping. Not sure what is going wrong. Any suggestions?

 

[Alex Blakenburg]

Can you elaborate on what you mean by they are disabled. The "By" box looks to be selectable in the image.
It looks to me that you are using the Data Model and Power Pivot.

If that is the case and you want to nominate Starting & Ending and Number of Days you will need to use either a Calendar Table and/or DAX.

 

[sharshra]

I should have made it clear! Group by is enabled. I'm able to click on different options (months, quarters, days etc). If I select group by month, data gets grouped by months.

I want to group by week, for which I have to select days & select number of days as 7. When I select days, 'Number of days' is disabled.

Also, I noticed that starting at & ending at are disabled always.

 

[Alex Blakenburg]

So are you using the Data Model and Power pivot ? If you are then that option does not seem to be available and you need to address it via the calendar table and/or DAX.

 

[sharshra]

I don't need a data model or power pivot for this simple pivot. Normal pivot table will suffice. How can I check if data model of power pivot is applied for this pivot table? If any of these are applied, I would like to convert it to a normal pivot table.

I may continue with data model or power pivot for other pivot tables in the spread sheet, but not for this pivot table.

 

[Alex Blakenburg]

If you try to change the data source is "Change Data Source" greyed out ?
If it is you are using the data model.

Another indication is if the Pivot Fields section has the options of Active & All and the you see the Table symbol in the fields list.

 

[sharshra]

Change data source is enabled & it allows me to change data source. So, data model is not used. But, still number of days is disabled.

I created a new pivot table, identical to the one where I have the problem & using the same data source. In the new pivot table, I can group by days & select number of days ? It's working for me now, but I really don't know what is the difference. My previous pivot table still has the same problem. Curious to know what would be the problem

 

[SpillerBD]

All dates are in date format,

Be careful of the difference between Cell formatting and an actual date in a cell.
A cell may be formatted for Date. The value in the Cell may look like a date. But the TEXT is not a date.
However, I am still baffled as having a Text value in the data would keep the Group by Date box from even coming up.
(I'm using O365. I do not remember if previous iterations of Excel behaved differently.)

 

[Alex Blakenburg]

Just grasping at straws but your image gives the impression you can select the start and end dates. Does it help if you tick those boxes and put in the Min & Max dates ?

 

[sharshra]

The start & finish dates were also disabled along with number of days.

When I created a new pivot table, number of days was enabled. Start & end dates were filled with correct dates. I need not select these explicitly. It's all good that it is working now as expected . But, still curious & struggling to understand why it didn't work before.