Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Cumulative Sum

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 DatePayment ReferencePayment Received12 Month Total PaymentWhat I want it to say!!!
09/05/202112345230351.64351.64

02/05/2021

12345 351.64121.64
25/04/202112345121.64351.64121.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. 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , 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]))
    )

     

  • Anonymous's avatar
    Anonymous
    5 years ago

    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]))
    )

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , 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]))
    )

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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]))
    )