One of the downsides of pivoting data using PowerPivot is that you cannot group fields. Grouping is especially important for date fields. You can however create a couple of calculated fields which will extract the year and month from a date.
Follow the tutorial and/or watch the video below.
Having got your data into the PowerPivot application, in the next available column, type Year as the column heading.
You can use the DAX function YEAR to extract the year from a date just as you can over in normal Excel. Your formula for this column will be =YEAR([Date]). I have assumed here that you have called your date field ‘Date’.
In the next available column, type Month as your column heading. You will need to use the DAX function FORMAT in this column which is the equivalent to the TEXT function over in normal Excel. The formula would be =FORMAT([Date],”MMM”)
By the time you are done, your calculated fields should be returning year and month for each record, as shown below.
To create a PivotTable, click on the PivotTable button (Home tab) and choose where to locate you PivotTable.
You can now add Year and Month as separate fields to your PivotTable. You will probably find the months are not sorted correctly. To fix this you will need to perform a custom sort on the month field.
Change the report layout setting to Show in Outline Form (DESIGN tab | Layout group)
Click on the Sort/Filter drop down button for the month field and select More Sort Options….
In the next dialog box select Ascending (A-Z) by: and make sure Month is selected in the drop down. Then click on More Options.
Now untick Sort automatically every time the report is updated
In the First key sort order drop down, select the list which displays months – there are two so make sure you pick the right one for your data.
Click OK to confirm.