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
JimB-GA
Helper III
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

 

DateTransactionTypeAmount
1/1/2018                 123Credit1500
1/1/2018                 124Debit420
1/3/2018                 125Debit360
1/4/2018                 126Credit1650
1/6/2018                 127Credit1200
1/10/2018                 128Debit1000

 

 

1 ACCEPTED SOLUTION
v-danhe-msft
Employee
Employee

Hi @JimB-GA,

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

Sample data:

1.PNG

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:

2.PNG

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

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.

View solution in original post

9 REPLIES 9
v-danhe-msft
Employee
Employee

Hi @JimB-GA,

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

Sample data:

1.PNG

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:

2.PNG

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

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.

Thanks so much.  A perfect solution.

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

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.
Zubair_Muhammad
Community Champion
Community Champion

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

rt.png


Regards
Zubair

Please try my custom visuals

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

@JimB-GA

 

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

 

DateTransactionTypeAmount
1/1/2018                 123Credit1500
1/1/2018                 124Debit420
1/3/2018                 125Debit360
1/4/2018                 126Credit1650
1/6/2018                 127Credit1200
1/10/2018                 128Debit1000

Regards
Zubair

Please try my custom visuals

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?

Sorry, I should have thought of that earlier!

 

DateTransactionTypeAmountBalance
1/1/2018                 123Credit         1,500         1,500
1/1/2018                 124Debit            420         1,080
1/3/2018                 125Debit            360            720
1/4/2018                 126Credit         1,650         2,370
1/6/2018                 127Credit         1,200         3,570
1/10/2018                 128Debit         1,000         2,570

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

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.