Is it possible to group dates into "Month - Year" format, without creating two separate groupings (by month and by year)

[omo]

The reason I don't want to have a separate year grouping is I have data from November-2016 to April-2017 and I want to show all months inside this period even if in some scenarios some months will have no data. But if I check "Use items with no data", I have the 24 months of 2016-17, which I don't need.

[MattAllington]

I don't understand what you are asking

[omo]

Sorry, I'll try to rephrase. I have some data for the period of November 2016 to April 2017 (daily). I need to group it in the pivot table. The usual grouping by month produces the following: Jan, Feb, March, Apr, Nov, Dec (in this order). This is pointless. I need to have this: Nov-2016, Dec-2016, Jan-2017, Feb-2017, March-2017, Apr-2017. The only (clumsy) way to achieve this that I know of is to group both by month and by year. This will produce two groupings (a hierarchy), instead of one simple grouping. Can it be done without having a separate year grouping?

[PhilC]

It would be interesting to see an actual file, as it seems what you term clumsy it perhaps what many of use would ordinarily use, ie nested fields (what you term hierarchy).

Potential options:
1. Not sure if this will work, but if you have a calendar table, order the Month field you are displaying by an appropriate field (ie YY-MM). Have not tested and am not 100% sure on this.
2. If no Calendar table, add a field to your data that represents YY-MM and use that field instead. It might work as a sort colum for the Month column (as per above) but again, not sure.

Cheers

[omo]

Here's the file. Note how the number of columns changes when the slicer is set to C or F. I need the number of columns to be the same all the time. Now right-click on Months and check "Show items with no data" and you end up with 24 months which I need even less.
This is just a sample of data, so I can't afford just to drag the months in their correct order by hand because there will be many more months and years, so I've got to see them in Jan-2017 format. So it's it's not just the sorting problem.

Your second option: why introduce a new column with YY-MM when I already have a regular date column that can be formatted YY-MM or any other of a large number of ways? Why can't Excel use that? Or do I misunderstand you somehow?

[PhilC]

Thanks for posting a workbook.

Firstly, you are using a Pivot Table, rather than a PowerPivot data model, so the responses here would not have anticipated that as this is a PowerPivot forum.

Attached is an approach that seems to work, using an additional field of YY-MM (not a data formatted, but effectively a text field).

If you want to understand how Show Items with no Data works with Date data perhaps Google that. I am not sure why the behaviour you are after does not work with the Date field, but it obviously does not.

Cheers
Phil

[omo]

Ok, Phil, thank you. In fact I trusted pivot tables not to be able to do that as well as some other basic things.