locked
Calculate closing balance RRS feed

  • 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 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 ?

    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