cancel
Showing results for
Did you mean:
Helper III

## Balance value calculation

Good morning all!

Still wet behind the ears with Power BI, I'm trying to create a column that maintains a running balance based on date and transaction type.  Here is my sample table.  My problem is how to calcluate based on the [Transsaction] field (debits v credits)

Thanks for any assistance.

JimB

 Date Transaction Type Amount 1/1/2018 123 Credit 1500 1/1/2018 124 Debit 420 1/3/2018 125 Debit 360 1/4/2018 126 Credit 1650 1/6/2018 127 Credit 1200 1/10/2018 128 Debit 1000

1 ACCEPTED SOLUTION
Microsoft

Hi @JimB-GA,

Based on my test, you could refer to below steps:

Sample data:

Create two calculated columns:

a = IF([Type]="Debit",-[Amount],[Amount])

Total running = CALCULATE(SUM(Table1[a]),FILTER('Table1','Table1'[Transaction]<=EARLIER(Table1[Transaction])))

And you could see the result:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 REPLIES 9
Microsoft

Hi @JimB-GA,

Based on my test, you could refer to below steps:

Sample data:

Create two calculated columns:

a = IF([Type]="Debit",-[Amount],[Amount])

Total running = CALCULATE(SUM(Table1[a]),FILTER('Table1','Table1'[Transaction]<=EARLIER(Table1[Transaction])))

And you could see the result:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper III

Thanks so much.  A perfect solution.

Microsoft

Hi @JimB-GA,

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Champion

@JimB-GA

Try this calculated column

```Running Total =
CALCULATE (
SUM ( Table1[Amount] ),
FILTER ( Table1, [Date] <= EARLIER ( [Date] ) && [Type] = EARLIER ( [Type] ) )
)```

Regards
Zubair

Helper III

I notified that I only used one ampersand where you formula used two.  Inserting a second & into the formula gave me a value.

I must confess that I was not clear with the calculation I want to perform.  Debits need to be subtracted from Credits for the balance column to work.  Do I need to create a calculated column for the amount changing the sign on debits?

Thanks again for you help.

JimB

Community Champion

@JimB-GA

What is the end result you desire? Could you show it in the sample table

 Date Transaction Type Amount 1/1/2018 123 Credit 1500 1/1/2018 124 Debit 420 1/3/2018 125 Debit 360 1/4/2018 126 Credit 1650 1/6/2018 127 Credit 1200 1/10/2018 128 Debit 1000

Regards
Zubair

He said what he needed in his initial post. It was clear. The desired end result is obvious. Why ask again and then never return to give an answer?

Helper III

Sorry, I should have thought of that earlier!

 Date Transaction Type Amount Balance 1/1/2018 123 Credit 1,500 1,500 1/1/2018 124 Debit 420 1,080 1/3/2018 125 Debit 360 720 1/4/2018 126 Credit 1,650 2,370 1/6/2018 127 Credit 1,200 3,570 1/10/2018 128 Debit 1,000 2,570
Helper III

Thank you Zabair.

The actual table name is 'Claire' and the type field is 'Transaction Type'.

I tried the following formula and received the error 'DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.'

Running Total =
CALCULATE (
SUM ( Claire[Amount] ),
FILTER ( Claire, [Date] <= EARLIER ( [Date] ) & [Transaction Type] = EARLIER ( [Transaction Type] ) )
)

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors