cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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

6 REPLIES 6
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Hi,
This is a Excel File with data sample.
Here is the graph I want to obtainThanks 😊

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

I did it manually 😂

Super User

How will i be able to help you then.  Unless i understand the logic, i cannot write the DAX measures.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.