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

Anything related to PowerPivot and DAX Formuale
omo
Posts: 8
Joined: Mon Jun 05, 2017 6:57 am

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

Postby omo » Tue Jun 13, 2017 11:06 pm

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
Posts: 873
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

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

Postby MattAllington » Wed Jun 14, 2017 10:05 am

I don't understand what you are asking
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

omo
Posts: 8
Joined: Mon Jun 05, 2017 6:57 am

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

Postby omo » Fri Jun 16, 2017 7:33 am

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
Posts: 145
Joined: Tue Sep 09, 2014 8:13 am

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

Postby PhilC » Fri Jun 16, 2017 11:01 am

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
Posts: 8
Joined: Mon Jun 05, 2017 6:57 am

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

Postby omo » Fri Jun 16, 2017 9:48 pm

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?
Attachments
Year-Month.xlsx
(21.58 KiB) Downloaded 8 times

PhilC
Posts: 145
Joined: Tue Sep 09, 2014 8:13 am

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

Postby PhilC » Tue Jun 20, 2017 9:17 am

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
Attachments
PPM Year-Month.xlsx
(28.03 KiB) Downloaded 8 times

omo
Posts: 8
Joined: Mon Jun 05, 2017 6:57 am

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

Postby omo » Thu Jun 22, 2017 8:08 am

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


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: masterelaichi and 2 guests