Runnig balance calculation

[rlambertini]

Hello,

I'm new to DAX, I want to calculate a running total column in a table with adjusted balance like the following:

Date	Adjusted Balance	Transaction		(desired column) Running balance
01/01/2020	1000	0		1000
02/01/2020		10		1010
05/01/2020		-5		1005
10/01/2020		5		1010
20/01/2020		10		1020
01/02/2020	1015	3		1018	Adjusted Balance resets the transaction sum
10/02/2020		-50		968
20/02/2020		2		970
01/03/2020	971	0		971	Adjusted Balance resets the transaction sum
17/03/2020		3		974

Could someone help me?

Thanks in advance

Roberto

[MFelix · Accepted solution]

Hi @rlambertini ,

Add the following column to your model:

Running_Balance = 

VAR CurrentDate = 'Table'[Transaction_Date]
VAR LastDateWithValue =
    CALCULATE (
        MAX ( 'Table'[Transaction_Date] );
        FILTER (
            'Table';
            'Table'[Adjusted Balance   ] <> BLANK () &&
                 'Table'[Transaction_Date] <= CurrentDate
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Adjusted Balance   ] );
        FILTER (
            'Table';
                 'Table'[Transaction_Date] = LastDateWithValue
        )
    ) + 'Table'[Transaction]

Result is below added your column for comparision reasons: