creating a calculated field in excel pivot table based on an item in a column

Question

Normally calculated pivot table fields in excel (2010) you click: design tab > options and sets > calculated field

and if you had three columns: name, sales, cost

name | sales | cost
josh    10      2    

your calculated field might be: profit which would be: = sales - cost.

However is it possible to make a calculated field which calculates the difference between two items in the one "Week Ending" column?

For example "Week ending" contains a dates which end on a friday 2015-05-01,2015-05-08,2015-05-15 etc and they are in the column labels section of the pivot table.

The "week ending" dates are all in the one column, is it still possible to calculate the difference between two of them with a calculated field?

name | End Fri 2015-05-01 | End Fri 2015-05-08 | calculated (Delta)
josh    8                              10           10-8 = 2

Answer 1

You do this with a Calculated Item. Here is an example that I think gets close to what you want. I selected one of the dates in order to get the menu to show up.

Here is the result

And here is a good reference about these. http://www.contextures.com/excelpivottablecalculateditem.htm