asked on

How to calculate Difference for two year in a Pivot Table using a Forumlas

I have a Pivot table that has the rows as users Columns label is Date (Year) and Sum of Values,
Values are sum per user for Bills and Sum per user Paid.

I need to calculate the difference between 2012 - 2013 paid as well as 2012 - 2013 Billed.

I see there is a Difference From option as well as using formulas.

Should I just do the calculation outside the PivotTable?

helpfinder

if you use some column outside of the pivot with formulas and references to the Pivot table cells in these formulas then you should calculate it without any problem

yo_bee

ASKER

All columns being references are within the PivotTable.

helpfinder

do you mean like this? (attached sample)
Book1.xlsx

yo_bee

ASKER

Yes

yo_bee

ASKER

Note: My dates are Grouped by Year, Month

helpfinder

OK, but in your screen I see only years - if you need to apply specificely to you your case, please attach your data (or sample) to check

yo_bee

ASKER

attached is the file
Book1.xlsx

ASKER CERTIFIED SOLUTION

cybermanoj4u

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

helpfinder

Hi, so now I extracted year from your dates and created pivot based on year
Check attachments. (2 versions attached)
Copy-of-Book1.xlsx
Copy-of-Book2.xlsx

yo_bee

ASKER

@HelpFinder:

That is what I was hoping to avoid. I want to use the Formula tool to create a field within the pivot table.

yo_bee

ASKER

I was not able to get to the final result from your solution, but it pointed me in the right direction.

Found what I was looking for.

I needed to Drag the Fees Bill Field and change it from sum to show value as, Selected the option Difference From and where I was going wrong before was choosing my base field

I just need to select Years (from the grouping) then Previous and it worked.

Thanks for the effort guys, but