none
Cannot group by date in pivot table

    Question

  • I would love to group these dates by month, but my group by option appears to be greyed out.

    I don't have any nulls and my data type is date.  I have tried changing the format around, but it doesn't seem to make a difference.


    Wednesday, June 04, 2014 7:45 PM

Answers

  • If you add a column to your date table in your Power Pivot model that has the month (which you could calculate with DAX if needed) then you can just drag that column onto the pivot table instead of using the raw date column.

    http://darren.gosbell.com - please mark correct answers

    Thursday, June 05, 2014 4:17 AM
    Answerer

All replies

  • If you add a column to your date table in your Power Pivot model that has the month (which you could calculate with DAX if needed) then you can just drag that column onto the pivot table instead of using the raw date column.

    http://darren.gosbell.com - please mark correct answers

    Thursday, June 05, 2014 4:17 AM
    Answerer
  • So that would just eliminate the need to group all-together?  That sounds reasonable I guess.

    The original date is in my table in this format. '1/1/2012 12:00:00 AM'

    Is the reason that it won't group in the first place because it includes the day and/or time in the data?

    One last question, I'm sorry.  Could you point me to a reference where I can see examples of DAX formulas to change my original format to month, or month/year?

    Thank you for your help.

    Monday, June 09, 2014 12:25 PM
  • Hi Heath,

    In a word, yes. The Pivot table will group over the distinct values in the column in which case 1/1/2012 12:00:00 AM and 1/1/2012 1:00:00 AM would be treated as different values despite being from the same day.

    You can create a column to sort the months and then create another column which returns the actual month names. For example, I have created a column called 'MonthNumber' using the following DAX fomrula:

    =VALUE(FORMAT('Table1'[Date],"yyyyMM"))


    I have then gone on to create a column called 'MonthName' with this DAX:

    =FORMAT([Date], "MMM yy")

    Finally, I set the sort property for the MonthName column to use the MonthNumber column to ensure it appears in the right order in your pivot tables and charts, otherwise the name would sort alphabetically.

    As always, this is one of many possible approaches. This particularly approach uses the FORMAT and VALUE DAX functions which you can learn more about here and here.


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn

    Wednesday, June 18, 2014 8:41 AM
    Moderator