Forum Discussion

eduardomarchesi's avatar
eduardomarchesi
Regular Visitor
3 years ago

Calculating Opening / Closing Balance

Hi team,   I'm trying to use a formule like that:          Closing Balance = ( Opening Balance   + field1 + field2 )   However the "Opening Balance" is the previous value of "Closing Balance", w...
Need Help
eduardomarchesi's avatar
eduardomarchesi
Regular Visitor
3 years ago

Hi amitchandak,

 

Thank you very much for your help!!

 

I've tried your suggestions but with no success.

As you can see below, I have got the previous values of "Closing Balance" and associated with "Opening Balance", so, this Opening Balance I need incluide in the formule to "Closing Balance" as you can check in the screen from excel example below.

PowerBI result:

Excel example:

 

Is it possible I send you my example and my data to take look and understand better the sctructure?

** I could not see the option to attach files here!! 

Rgds

Eduardo

 

 

 

PBISunK's avatar
PBISunK
Frequent Visitor
1 year ago

I know this is an old post but wnating to see if this was successfully handled in the code? I have exactly same requirement and dealing with circular dependency issues

    • LUCAS_ROSSETTI's avatar
      LUCAS_ROSSETTI
      New Member
      11 months ago

      Thanks, some adjustments to my case but you saved me , I was about to give up lol

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        11 months ago

        You are welcome.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Here is how I handled the this issue. 

    Opening Balance = 
VAR MinDateInContext = MIN('Table'[Date])
VAR EndOfPrevMonth = EOMONTH(MinDateInContext, -1)
VAR BaseAmount = 
    CALCULATE(
        SUM('Table'[Milk Amount]) + SUM('Table'[Bread Amount]) + SUM('Table'[Butter Amount]) , 
        FILTER(
            ALL('Table'), 
            'Table'[Date] <= EndOfPrevMonth
        )
    )
RETURN
    IF(
        ISBLANK(BaseAmount),
        BLANK(),
        BaseAmount + 500
    )

     

    Closing Balance = 
VAR MaxDateInContext = MAX('Table'[Date])
VAR EndOMonth = EOMONTH(MaxDateInContext, 0)
VAR BaseAmount = 
    CALCULATE(
        SUM('Table'[Milk Amount]) + SUM('Table'[Bread Amount]) + SUM('Table'[Butter Amount]), 
        FILTER(
            ALL('Table'), 
            'Table'[Date] <= EndOMonth
        )
    )
RETURN
    IF(
        ISBLANK(BaseAmount),
        BLANK(),
        BaseAmount + 500
    )
    • PBISunK's avatar
      PBISunK
      Frequent Visitor
      1 year ago

      Thanks so much for the code example. I will try and see. Was thinking about cummulative sum until the period end from start. Also, realized could do all the calculations based on Ending Balance alone and just refer the Next month's Beginning Balance to be prev month's ending balance without any complications to the code - thus probably avoiding the circular dependency issue.