Forum Discussion

sigtopo's avatar
sigtopo
Frequent Visitor
2 years ago

Continuous cumulative values

I have a table which contains bank transactions from several accounts (date,  account number,transaction name, value).
For sample :
01/15/2023;Account01;Transaction n°1;-500$
02/10/2023;Account02;Transaction B1;-50$
03/01/2023;Account02;Transaction B2;+550$
03/03/2023;Account01;Transaction n°2;+1500$
03/08/2023;Account01;Transaction n°3;-200$
09/01/2023;Account01;Transaction n°4;+500$

10/04/2023;Account05;Transaction B3;-70$

I have another table which contains the balance of each account on the eve of the first date of the bank transactions table (to initialize the balances).
For sample :
01/01/2023 ;Account01;1000$
01/01/2023 ;Account02;800$

I would like to be able to display a stacked graph of the evolution of the balances of all accounts (one color per account).
Currently, since I don't have a value for each day, the graph is not continuous.
Thanks

Need Help

6 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    2 years ago

    You probably want a date table with a relationship to both tables (you could also consider appending your tables so that the initial balance is the 0th transaction). Then you can write a cumulative measure with the handy WINDOW function.

     

    Cumulative Balance =
CALCULATE (
    SUM ( Table1[Amount] ),
    WINDOW ( 1, ABS, 0, REL, ALL ( DateTable ), ORDERBY ( DateTable[Date] ) )
) + SUM ( Table2[Amount] )

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        2 years ago

        Hi,

        How have you arrived at the numbers in column E:F of the Graph worksheet.  Please show the Excel formulas for those columns so that i can translate those in the DAX language.