cancel
Showing results for 
Search instead for 
Did you mean: 
DRay
Community Team Member
Community Team Member

Question

How to Calculate YTD by Month in a Pivot Table?

Answer

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.

DRay_0-1713991552654.png

 

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

DRay_1-1713991552810.png

Custom function:

=RPSUM([Total BUDGET],12)

This formula takes the monthly budget for the year (as filtered by on my dashboard filter) and cumulatively adds it to the previous month's budget and cumulates from there. =RPSUM([Total BUDGET]) was working, but only for 2 months at a time (Jan + Feb, reset for March + April, etc). By adding the 12 it knows to repeat after 12 periods, so it takes care of the entire year.

Apply the RPSUM with 12 periods to my Budget, Revenue, and Variance (Revenue - Budget) columns.

 

Rate this article:
Version history
Last update:
‎04-24-2024 02:47 PM
Updated by:
Contributors