Calculating Opening / Closing Balance

[eduardomarchesi]

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", when I try to get the previous value for closing balance, it is showing a error: "a circular occurrence was detected"..

Do you now how I can fix it?

Rgds

Eduardo

[Anonymous]

Hi, just wondering if you could figure out the way to calculate the opening balance and closing balance the way you wanted?
Please let me know.

Thanks.

[amitchandak]

@eduardomarchesi , If this is recursive calculation like excel, that is not possible.

If it can be cumulative 

Opening = CALCULATE(SUM(Sales[Amount]),filter(all('Date'),'Date'[date] <Min ('Date'[date])))

Closing =  CALCULATE(SUM(Sales[Amount]),filter(all('Date'),'Date'[date] <= Max('Date'[date])))

Or you can consider

openingbalancemonth, openingbalancequarter, openingbalanceyear: https://youtu.be/6lzYOXI5wfo
Power BI Allocating Targets- closingbalancemonth, closingbalancequarter, closingbalanceyear:https://youtu.be/yPQ9UV37LOU

[eduardomarchesi]

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]

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

[Ashish_Mathur]

Hi,

I have solved a similar question in the attached files.

Hope this helps.

[LUCAS_ROSSETTI]

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

[Ashish_Mathur]

You are welcome.

[Anonymous]

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]

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.