Calculate balance of account

[MPR]

Hello,

I am new to this forum and fairly new to Power BI so please excuse my ignorance.  I have a problem with a DAX formula that is killing me.  

I am trying to calculat a current balance of a bank account.  I was hoping there would be a way to upload my pbix file but I don't see how to do that so I will try to explain.  

Following is a table of data:

Index	Date	Amount	Balance
265	4/19/2018	$1,214.55
264	4/19/2018	$2,436.80
263	4/19/2018	$1,211.35
262	4/19/2018	$459.00
261	4/19/2018	$811.00
260	4/19/2018	$986.59
259	4/19/2018	($35.00)
258	4/19/2018	($1,549.73)
257	4/19/2018	$2,821.25
256	4/19/2018	$7,976.00
255	4/18/2018	$1,182.07	$91,517.55
254	4/18/2018	$2,156.25	$93,673.80
253	4/18/2018	$1,258.95	$94,932.75
252	4/18/2018	$2,096.66	$87,594.94
251	4/18/2018	$1,490.82	$89,085.76
250	4/18/2018	$1,249.72	$90,335.48
249	4/18/2018	$181.48	$97,201.23

This info assumes today is Apr 19.  With this data, I am trying to build a couple of formulas.  

First is a formula that provides the balance through yesterday.  For today's (Apr 19) transactions, it won't show the balance because the transactions are still pending.  To get yesterday's balance (in this example, I am trying to get $91,517.55), I created two formulas.

First, I wanted to get the max index so I get the correct balance>

Max Index = CALCULATE(MAX('Statements'[Index]),FILTER('Statements','Statements'[Balance] <> BLANK()))

Then, using the max index, I want to get the balance ($91,517.55).  This is the formula that I think I am getting wrong.  

 Bank Balance = CALCULATE(MIN('Statements'[Balance]),FILTER('Statements','Statements'[Index] = [Max Index]))

I am still working through how the nuances of the contexts and how calculate changes them but my gut is telling me there is a context problem with this formula.

Once I get yesterday's balance, I can add the pending amounts to get the actual balance.  For that formula, I have 

Pending = CALCULATE(SUM(Statements[Amount]),FILTER('Statements','Statements'[Balance] = BLANK()))

I think that formula is okay.  I am getting the correct result with that.

I would really appreciate someone's help letting me know what I am doing wrong in the second formula.

Thanks,

MPR

[MPR]

Thanks drewlewis15.  That did it.  

[drewlewis15 · Accepted solution]

You can probably continue using your evaluation to find the last ending balance by checking to see if balance is simply blank, but I added a custom column in the query editor to identify if the record is "pending" or "posted".

if Balance = null then "Pending" else "Posted"

I then created the following measure, which seems to be grabbing the last ending balance.

Last Ending Balance = LOOKUPVALUE(Statements[Balance], Statements[Index], (CALCULATE(MAX(Statements[Index]), FILTER(Statements, Statements[Status] = "Posted"))))

Probably a more efficient way to write this, but it is getting the correct amount in my testing.  From there, your other measure should be able to add the pending total to the Last Ending Balance total.