Cumulative total re-starting by year?

macfuller

Active Member
Joined
Apr 30, 2014
Messages
290
Office Version
  1. 365
Platform
  1. Windows
I have a standard cumulative measure using a date table AP_Calendar against vouchers. The [Voucher Charges Total Paid Amt] is a simple SUM of vouchers we have paid.
VBA Code:
Cumulative Paid Amt:=CALCULATE (
    [Voucher Charges Total Paid Amt],
    FILTER (
        ALLSELECTED ( 'AP_Calendar'[Date] ),
        'AP_Calendar'[Date] <= MAX ( 'AP_Calendar'[Date] )
    )
)

However, I would like to compare 2019 to 2020 cumulative amounts by simply adding the AP_Calendar[Year] field to my pivot table column to get

2019 Sum2019 Cumulative Sum2020 Sum2020 Cumulative Sum
Jan
1​
1​
2​
2​
Feb
2​
3​
1​
3​
Mar
1​
4​
2​
5​
and so on.

I've tried various combinations of KEEPFILTERS or specifically calling out the year, such as:
VBA Code:
Cumulative Paid Amt:=CALCULATE (
    [Voucher Charges Total Paid Amt],
    FILTER (
        ALLSELECTED ( 'AP_Calendar'[Date] ),
        'AP_Calendar'[Date] <= MAX ( 'AP_Calendar'[Date] )
        &&     year ( MAX( Vouchers[Payment Date] ) ) = year ( AP_Calendar[Date] )
    )
)
but it's not giving me what I expect. I appreciate a correction!
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Watch MrExcel Video

Forum statistics

Threads
1,118,755
Messages
5,574,043
Members
412,565
Latest member
roberttaekim
Top