How can i calculate the balance in excel's powerpivot?

Question

I'm doing this in powerpivot Excel 2013

So i have a table what looks a little like this:

name    debit/credit  amount    Date
Jane    C             €10,00    01-01-2013 01:00
Jane    C             €10,20    01-01-2013 06:20
Jane    D             €12,30    03-01-2013 14:13
ETC

The table is sorted by date
I want to create an extra column in my data model so the table looks like this:

name    debit/credit  amount    Date                Balance
Jane    C             €10,00    01-01-2013 01:00    €10,00
Jane    C             €10,20    01-01-2013 06:20    €20,20
Jane    D             €12,30    03-01-2013 14:13    €7,90
ETC

Any advice how to do this?

Answer 1

You need to use DAX CALCULATE for this.

To simplify it a bit I first added a column:

=if([debit/credit]="C",[amount],-[amount])

Then I did balance with this:

Balance = CALCULATE(sum([adjAmount]),FILTER('Table1',[date]<=EARLIER([date])))

It is possible to do it with a single formula but it is more complicated than my sluggish brain can handle now.

UPDATE Sorry, I missed out on the condition for handling multiple names.

Balance =CALCULATE(sum([adjAmount]),FILTER('Table1',AND([date]<=EARLIER([date]),[name]=EARLIER([name]))))

Answer 2

Just use two SUMIF functions, one to sum the credit and one to sum the debit, then subtract the debit from the credit. Use one absolute reference and one relative so that when it is copied down the column the absolute reference remains the same. Enter this into E2 and drag down:

=SUMIF($B$2:B2,"C",$C$2:C2)-SUMIF($B$2:B2,"D",$C$2:C2)