Cumulative Sum

[DLROLLINGS]

Hi all,

I am having trouble using the cumulative sum of a set period, I have looked at various solutions on here but with each that I use, it is suming the last 12 months as I wish, however this is applying this figure to every date range, ie if the balances summed 10/05/2020 is exactly the same as the balance summed 09/05/2021 (its a weekly balancing every Sunday). 

The lastest attempt I am using is

Annual Payment =

VAR EarliestDate = Balances[Last Balance Date]-365

RETURN

CALCULATE(

SUM(Balances[Payments Received]),

FILTER(Balances,Balances[Last Balance Date]>EarliestDate && Balances[Payment Reference]=Balances[Payment Reference])

)

Sample Data Below - Not this includes one reference number I only want the cumulative sum of each reference so that I can apply region filters are required. 

Pay

Last Balance Date	Payment Reference	Payment Received	12 Month Total Payment	What I want it to say!!!
09/05/2021	12345	230	351.64	351.64
02/05/2021	12345		351.64	121.64
25/04/2021	12345	121.64	351.64	121.64

The raw data has over 23000 reference numbers and goes back over 12 months, but I want the rolling total for each account to be other the last 12 months. Currently, every single record has the same figure throughout the table.

I have tried the same as above using a calculated table and using table = distinct(last balance date) but I get the same result. Any help greatly appreciated. 

[amitchandak · Accepted solution]

@DLROLLINGS , I am assuming this is a new column

Try

Annual Payment =
VAR EarliestDate = Balances[Last Balance Date]-365
RETURN
CALCULATE(
SUM(Balances[Payments Received]),
FILTER(Balances,Balances[Last Balance Date]>EarliestDate && Balances[Last Balance Date] <=earlier(Balances[Last Balance Date]) && Balances[Payment Reference]=earlier(Balances[Payment Reference]))
)

or

Annual Payment =
VAR EarliestDate = Balances[Last Balance Date]-365
RETURN
CALCULATE(
SUM(Balances[Payments Received]),
FILTER(Balances,Balances[Last Balance Date]>EarliestDate && Balances[Payment Reference]=earlier(Balances[Payment Reference]))
)

[DLROLLINGS · Accepted solution]

Cheers Amit, worked like a charm. 

For reference, I used the following:

Annual Payment =
VAR EarliestDate = Balances[Last Balance Date]-365
RETURN
CALCULATE(
SUM(Balances[Payments Received]),
FILTER(Balances,Balances[Last Balance Date]>EarliestDate && Balances[Last Balance Date] <=earlier(Balances[Last Balance Date]) && Balances[Payment Reference]=earlier(Balances[Payment Reference]))
)

[amitchandak · Accepted solution]

@DLROLLINGS , I am assuming this is a new column

Try

Annual Payment =
VAR EarliestDate = Balances[Last Balance Date]-365
RETURN
CALCULATE(
SUM(Balances[Payments Received]),
FILTER(Balances,Balances[Last Balance Date]>EarliestDate && Balances[Last Balance Date] <=earlier(Balances[Last Balance Date]) && Balances[Payment Reference]=earlier(Balances[Payment Reference]))
)

or

Annual Payment =
VAR EarliestDate = Balances[Last Balance Date]-365
RETURN
CALCULATE(
SUM(Balances[Payments Received]),
FILTER(Balances,Balances[Last Balance Date]>EarliestDate && Balances[Payment Reference]=earlier(Balances[Payment Reference]))
)