SOLVED

# Pivot Table Turnover 23-22

Copper Contributor

# Pivot Table Turnover 23-22

Hello together,
I have the following problem. I have sales figures of my company displayed in a pivot table, based on the year. Unfortunately, I only have one field in which the turnover is displayed, which is why it is not possible for me to create a difference between year 2023 and year 2022. Is there a way to create a difference, possibly using an auxiliary table, etc.? See also the screenshots. Unfortunatly I can not get the data based on the year because I just get every sales line from our system with a certain buyer and date where it has been bought.

This is the Pivot table where a Sum is drawn based on the Item and the year.

10 Replies

# Re: Pivot Table Turnover 23-22

Click on 2022 or 2023.

On the PivotTable analyze tab of the ribbon, click Fields, Items & Sets > Calculated Item...

In the Name box, enter Difference or something similar.

In the Formula box, enter =Year[2023]-Year[2022]

(This syntax is necessary because 2022 and 2023 are numbers. If you entered =2023-2022, the result would be 1)

# Re: Pivot Table Turnover 23-22

@Hans Vogelaar Hi and thanks for the quick reply. Unfortunatly Excel can't address the Year field. I get the following error:

# Re: Pivot Table Turnover 23-22

Does it work if you use

=Jahr[2.023]-Jahr[2.022]

# Re: Pivot Table Turnover 23-22

Good morning Hans,

no it doesn't work. Either I get the error that it can't resolve the Year or if I put it in "" I get a Value Error because it is seen as Text. I don't get why Excel has a SUM function but not a difference function...

# Re: Pivot Table Turnover 23-22

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

# Re: Pivot Table Turnover 23-22

I have the example attached to the original post.
best response confirmed by carlstegen (Copper Contributor)
Solution

# Re: Pivot Table Turnover 23-22

This worked for me: ='2023'-'2022'

I have attached the workbook, but you'll want to create the formula yourself, since the labels were changed to English in my version.

Note that I turned off the grand total for rows, because Excel would include the Differenz value in the grand total.

# Re: Pivot Table Turnover 23-22

Thanks alot Hans! You are the best!

# Re: Pivot Table Turnover 23-22

One last question: I think I found what seems to be the issue for me. I created the formula in a calculated field. However, in the excel example which you gave me you created a calculated element. How did you do that? Because under the Pivotheader Fields, Elements and Groups I can not select the option "Elements".

# Re: Pivot Table Turnover 23-22

I clicked on 2023 (2022 would have been OK too), then selected Fields, Items & Sets > Calculated Item...