cancel
Showing results for 
Search instead for 
Did you mean: 

How to Calculate YTD by Month in a pivot table

oliviar
7 - Data Storage
7 - Data Storage

I have a pivot table that works for my team. It looks like this:

oliviar_0-1654019798132.png

The variance column is a simple formula showing the difference between Budget and Revenue (Revenue - Budget).

I have received a request to expand this pivot (or create a new one based off of the data in this current one) that calculates the YTD for each of these fields. 

So, for example, I'm trying to build 3 new columns in the pivot table:
March Budget YTD = January Budget + February Budget + March Budget
March Revenue YTD = January Revenue + February Revenue + March Revenue
March Variance YTD = March Revenue YTD - March Budget YTD

I need these additional columns for each month, either added to this existing pivot table, or in a new one with a filter for the month the team wants to look at.

Is this possible in a pivot table? I need to keep it split by Customer Type.

How would I solve for this?

 

 

1 ACCEPTED SOLUTION

gwolfe
9 - Travel Pro
9 - Travel Pro

Hey @oliviar , 

If you want to show YTD by Month next to your monthly amount you can utilize the "Running Sum" Quick Calculation. If you want to show this next to the monthly amount, just bring in that same value again.

gwolfe_1-1654095823312.png

If you just want to show total for the time period you can use the "Grand Total" on columns

gwolfe_2-1654095986827.png

Hope this helps

View solution in original post

1 REPLY 1

gwolfe
9 - Travel Pro
9 - Travel Pro

Hey @oliviar , 

If you want to show YTD by Month next to your monthly amount you can utilize the "Running Sum" Quick Calculation. If you want to show this next to the monthly amount, just bring in that same value again.

gwolfe_1-1654095823312.png

If you just want to show total for the time period you can use the "Grand Total" on columns

gwolfe_2-1654095986827.png

Hope this helps