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.
Technology Tips and News
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.
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.
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 her