Grouping Dates in Power Pivot

[kersplash]

Hi,

I currently have a spreadsheet that I've created that uses pivot tables and I'm able to group dates by months, years, etc.

I am trying to simplify my spreadsheet by using Power Pivot for the first time.

I have a field that only has dates in it and I add it to the pivot table in Power Pivot.

But I am unable to group the field as anything (see screenshot)

In the example I tried I created a simple table with one date in it and added to Power Pivot table rows area.

Untitled.jpg

[benishiryo]

hi there. yes, you can't group dates after you use PowerPivot. i don't have PowerPivot installed in my computer right now, so i can't confirm. But i think the dates are being recognized as texts after using PowerPivot. i usually do a separate table with all these information.

Date	Month Number	Month	Year
1-Jan-2018	1	Jan	2018
2-Jan-2018	1	Jan	2018
3-Jan-2018	1	Jan	2018

the 1st column is a list of unique dates. 2nd column is done via DAX formula in PowerPivot
=MONTH(cell)
3rd column is done via DAX formula in PowerPivot
=FORMAT(cell,"mmm")
4th column is done via DAX formula in PowerPivot
=YEAR(cell)

but the 3rd column is merely a text and does not work like the normal "group" where it recognizes Jan as the 1st month in a year. it recognizes Apr as the 1st one as it is by alphabetical order. hence, you need to sort your date table you just created according to the month number (2nd column). create a relationship between your 1st table's dates and this 2nd table's dates.

i'm afraid this is as much as i can share without screenshots as i don't have it installed.

[kersplash]

Do you mean I need to do this for every date field I need to group?

I have many date fields that need grouping.