# Calculate closing balance

• ### Question

• Hi everyone,

I've got a problem with writting a formula wich will calculate a closing balance for each account side, I mean debet and credit. In my SQL database I've got columns with: OpeningBalanceDt, OpeningBalanceCt , DtTurnover, CtTurnover, IDaccount and TIMEID. When I create a Calculated Field:

`BalanceDt:=IF(([OBDt+Dt]-[ObCt+Ct])>0;[OBDt+Dt]-[ObCt+Ct];0)`
`BalanceCt:=IF(([ObCt+Ct]-[OBDt+Dt])>0;[ObCt+Ct]-[OBDt+Dt];0)`

it works for particular account eg:11575, but it doesn't when I remove filter and check global account nr 111 (then I have per account result).

Do you have any idea what formula I need to write to have a proper view it pivot table for both account side ? Meaby I need to create a calculate column not filed...

The problem concerns the fact that pp check columns globally and then compare calculeted sum. In this case global sum of BalanceDt has a higher value than BalanceCt so the final balance is Dt.

In excel I can calculate 2 columns:

`=if((OBDt+Dt-(ObCt+Ct))<=0;0;OBDt+Dt-(ObCt+Ct))`
`=if((OBCt+Ct-(ObDt+Dt))<=0;0;OBCt+Ct-(ObDt+CDt))`
and when I create a sum I've got a proper result. Maybe the solution will be in grouping the accounts and then conduct a comparision.

Paul

• Edited by Monday, May 26, 2014 10:49 AM
Monday, May 26, 2014 9:00 AM

• I found a solution:

`SaldoWNNar:=SUMX(KontoFinansowe;IF([Suma ObrotyWNNar]>[Suma ObrotyMANar];[Suma ObrotyWNNar]-[Suma ObrotyMANar]))`

where KontoFinansowe is a whole table and ObrotyWNNar and ObrotyMANar are measures.

See you soon

• Proposed as answer by Friday, June 13, 2014 9:21 AM
• Marked as answer by Sunday, June 22, 2014 10:21 PM
Wednesday, June 11, 2014 1:15 PM

### All replies

• Hi Paul,

I probably do not understand your entire scenario but could you explain why your Excel-approach (two columns with different IF-conditions that are summarized) does not work in Power Pivot? Could you upload a (simplified) example?

Regards,

Julian

Julian Wissel | BI for NAV @ http://en.navbi.com | Blog @ blog.navida.eu

Tuesday, May 27, 2014 6:42 PM
• Not entirely sure about the scenario as I can't distinguish the table but it sounds like you might end up with some pretty wicked/weird scenarios at a total and subtotal level.

I second Julian-s motion

Monday, June 2, 2014 10:00 AM
• Hi,

I will change a subject a little bit. Now I have the following columns: ObrotyKontID, WN, MA, BO_WN, BO_MA, KontoFinansoweID, KontoSyntetyczneID and TIMEID. Guy from ERP wrote two formulas which works perfect in system and calculate closing balance for each account side:

Debit

`Sum(Filter(existing[KontoAnalityczne],[Measures].[ObrotyWNNar]>[Measures].[ObrotyMANar]),[Measures].[ObrotyWNNar]-[Measures].[ObrotyMANar])`

Credit

`Sum(Filter(existing[KontoAnalityczne],[Measures].[ObrotyWNNar]<[Measures].[ObrotyMANar]),[Measures].[ObrotyMANar]-[Measures].[ObrotyWNNar])`

and 2 more formulas to calculate Debit and Credit

ObrotyWNNar

```Sum(StrToMember("[Czas].[Miesiąc].&["+left(Tail(existing [SET_Data]).Item(0).Properties("KEY"),4)+"01]"
):StrToMember("[Czas].[Miesiąc].&["+left(Tail(existing [SET_Data]).Item(0).Properties("KEY"),6)+"]"
),[Measures].[ObrotyWN]+[Measures].[BO WN])
```

ObrotyMANar

```Sum(StrToMember("[Czas].[Miesiąc].&["+left(Tail(existing [SET_Data]).Item(0).Properties("KEY"),4)+"01]"
):StrToMember("[Czas].[Miesiąc].&["+left(Tail(existing [SET_Data]).Item(0).Properties("KEY"),6)+"]"
),[Measures].[ObrotyMA]+[Measures].[BO MA])
```

I have got the same sum in ObrotyWNNar by forumla

`=[WN]+[BO_WN]`

and ObrotyMANar by

`=[MA]+[BO_MA]`

However I can't create proper formulas in powerpiwot which calculate Debit and Credit.  I suppose that I need to use some agregate formulas like SUMX.

If anyone have any idea ?

Paul

Wednesday, June 11, 2014 11:57 AM
• I found a solution:

`SaldoWNNar:=SUMX(KontoFinansowe;IF([Suma ObrotyWNNar]>[Suma ObrotyMANar];[Suma ObrotyWNNar]-[Suma ObrotyMANar]))`

where KontoFinansowe is a whole table and ObrotyWNNar and ObrotyMANar are measures.

See you soon

• Proposed as answer by Friday, June 13, 2014 9:21 AM
• Marked as answer by Sunday, June 22, 2014 10:21 PM
Wednesday, June 11, 2014 1:15 PM