Forum Discussion

fazza1991's avatar
fazza1991
Icon for Helper II rankHelper II
4 years ago
Solved

Calculated Column Cumulative /Running total by customer

Hi,

 

I am trying to do a calculated "COLUMN" to get a running total by customer and date from FIRST INVOICE DATE to the CURRENT INVOICE DATE.

 

DateAccount_IdAccount_NameNet_RevenueFirstInvoiceLastInvoice
31/10/2013INSTARINSTAR200031/10/201331/12/2013
30/11/2013INSTARISNTAR200031/10/201331/12/2013
31/12/2013INSTARINSTAR200031/10/201331/12/2013
31/10/2013FINSTARFINSTAR100031/10/201330/11/2013
30/11/2013FINSTARFINSTAR200031/10/201330/11/2013

 

 

I would expect the following results but i just cant seem to get it right

 

DateAccount_IdAccount_NameRevenueFirstInvoiceLastInvoiceRunning_Total
31/10/2013INSTARINSTAR200031/10/201331/12/20132000
30/11/2013INSTARINSTAR200031/10/201331/12/20134000
31/12/2013INSTARINSTAR200031/10/201331/12/20136000
31/10/2013FINSTARFINSTAR100031/10/201330/11/20131000
30/11/2013FINSTARFINSTAR200031/10/201330/11/20133000

 

Any help at this point would be great

 

Note I am specifically looking for calculated COLUMN.

 

Thanks

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    fazza1991 ,

    a new column

    = sumx(filter(Table, [Account_Id] = earlier([Account_Id]) && [Date] <= earlier([Date])  ) , [Net_Revenue] )

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    fazza1991 ,

    a new column

    = sumx(filter(Table, [Account_Id] = earlier([Account_Id]) && [Date] <= earlier([Date])  ) , [Net_Revenue] )