Cumulative total re-starting by year?

macfuller

Active Member
Joined
Apr 30, 2014
Messages
291
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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Watch MrExcel Video

Forum statistics

Threads
1,119,118
Messages
5,576,198
Members
412,706
Latest member
msousa25
Top