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.
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
and so on.
I've tried various combinations of KEEPFILTERS or specifically calling out the year, such as:
but it's not giving me what I expect. I appreciate a correction!
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 Sum | 2019 Cumulative Sum | 2020 Sum | 2020 Cumulative Sum | |
Jan | 1 | 1 | 2 | 2 |
Feb | 2 | 3 | 1 | 3 |
Mar | 1 | 4 | 2 | 5 |
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] )
)
)