Let’s say the data sheet has Sales and Profit data by Product, Region, Salesman and Date. One may want to analyse the following via a Pivot Table
“The Sales Delta by year, month, salesman and product i.e. for May 2012, compute the [Sales in May 2012] less [Sales in May 2011] for every salesman and product. Do the same for every month for two year (2012 and 2013).”
While this is easy to accomplish using the “Show Value As” feature of a Pivot Table, the problem arises when a particular year is filtered out from the Pivot Table. This activity of filtering out a year from the Pivot Table causes the other computed Sales Delta figures to vanish.
This problem can be overcome by creating a Pivot Table from the PowerPivot tool of Excel 2010 and higher versions.
You may refer to the question, Pivot Table attempt and PowerPivot solution in this workbook.