Creating difference / variance columns in pivot

[obionenairobi]

I have a challenge for which I need some help

1. I have an excel which lists all sales orders for a 2 year period

2. I have created a pivot which shows for my top 40 customer (sorted by sales amount) the sales and margin in previous year and current year

3. I would like to add 2 columns inside the pivot (on the right side) for the variance between this year and previous year for sales and margin (see excel attached)

Can you please advise me how to achieve this and keep the layout as shown in the file?

[xlnitwit]

Hi,

Is it OK to add a new column to your source data?

[obionenairobi]

What would you like to add?

[xlnitwit]

A column to calculate the year. You need a calculated item in the pivot table to calculate 2016-2015 but you can't add a calculated item while the date field is grouped.

[obionenairobi]

That should not be a problem

See attached

[xlnitwit]

Do you know how to create a calculated item?

[stasinek]

Hello,
See change your source data and formulas in the fields of computing

[obionenairobi]

I have done some simple ones

How can you do a calculated item to calculate the difference between current year and previous year for the sales or margin?

[xlnitwit]

Once you've ungrouped your date field and removed it from the pivot, add the new year column to the pivot in its place. Then select one of the year cells in the pivot and add a calculated item (not calculated field) called variance using the formula ='2016'-'2015' and that will produce the result you want.

[obionenairobi]

Ok, I think I got it but lets make this future proof

What if we are in 2017 and I want to compare 2017 vs 2016 or if I want to compare 2015 vs 2014
This Excel is linked against a database table where I filter on start and end date and when I refresh the data for a 2 year period is pulled of the database in what I call the datasheet and then the pivot will be updated on the data in the data sheet

Not sure if this is clear but it is still early :-(

[xlnitwit]

I have just looked at your new file- the data layout seems to be completely different from the first one.

With the original layout, if you will only have two years in the source data at any time you could define the calculated item using the formula =Year[2]-Year[1] and it will always subtract the earlier year from the later one.

[obionenairobi]

I am sorry to create extra confusion but I created a Version 2 of the file which I cleaned up and followed your instruction to created the calculated item
The only thing that I did not manage to do is working with different years re your last reply

[xlnitwit]

Unfortunately, it is not possible to use the relative item references when one of your fields is set to autosort. You could add a new column that labels the data as Current year and previous year, and then use those in the item calculation, or use some code to update the calculation.

I think those are the only real options in Excel 2007, other than perhaps adding the data fields again and choosing to display them as the Difference from the previous field. You would need to hide some columns to maintain the current layout.