Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rlambertini
New Member

Runnig balance calculation

Hello,

 

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

 

DateAdjusted 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     1018Adjusted Balance resets the transaction sum
10/02/2020       -50      968 
20/02/2020         2      970 
01/03/2020           971        0      971Adjusted Balance resets the transaction sum
17/03/2020         3      974 

 

Could someone help me?

 

Thanks in advance

Roberto

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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:

running.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

1 REPLY 1
MFelix
Super User
Super User

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:

running.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.