Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |