Balance value calculation

[JimB-GA]

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

[v-danhe-msft · Accepted solution]

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:

You can also download the PBIX file to have a view.

 https://www.dropbox.com/s/j4kh8ait0z2f69w/Balance%20value%20calculation.pbix?dl=0

Regards,

Daniel He

[JimB-GA]

Thanks so much.  A perfect solution.

[v-danhe-msft]

Hi @JimB-GA,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[Zubair_Muhammad]

@JimB-GA

What are your expected results

Try this calculated column

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

[JimB-GA]

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

[Zubair_Muhammad]

@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

[polymathy2017]

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?

[JimB-GA]

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

[JimB-GA]

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