Answered by:
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.
Thanks in advice,
Paul
 Edited by wrybel Monday, May 26, 2014 10:49 AM
Monday, May 26, 2014 9:00 AM
Answers

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 Michael Amadi Friday, June 13, 2014 9:21 AM
 Marked as answer by Michael Amadi 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 Excelapproach (two columns with different IFconditions 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 Julians 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 ?
Thanks in advice,
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 Michael Amadi Friday, June 13, 2014 9:21 AM
 Marked as answer by Michael Amadi Sunday, June 22, 2014 10:21 PM
Wednesday, June 11, 2014 1:15 PM