I am trying to view the difference in Sales from one period to the next in an Excel PivotTable. Transaction dates are grouped by Years/Quarters. Years and Quarters are Row Labels in the PivotTable. The PivotTable Values are the Sum of Sales. Values are shown as the Difference From, with the Base Field as Quarters and the Base Item as (previous). This works fine when the data is limited to a single fiscal year, but I experience a problem when the data spans a fiscal year (example: from Q4'2011 to Q1'2012). In this example, when spanning a fiscal year, the PivotTable does not calculate the difference Q1'2012 from Q4'2011. Please reference below for an illustration. I know this could be solved by adding a function similar to the following to the dataset =YEAR(A1)&"-Q"&INT(MONTH(A1)/4+1. However, I would like to avoid such an approach, if possible. If anyone has a solution to this problem, I would be greatly appreciative.
<COLGROUP><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><TBODY>
</TBODY>
| Row Labels | Sales |
| 2011 | |
| Qtr1 | |
| Qtr2 | -15,592.16 |
| Qtr3 | 1,151.75 |
| Qtr4 | 156,429.46 |
| 2012 | |
| Qtr1 | |
| Qtr2 | 61,999.37 |
| Qtr3 | -41,808.15 |
| Qtr4 | -105,343.56 |
<COLGROUP><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><TBODY>
</TBODY>
