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