Calculating Opening / Closing Balance

Question

Hi team,&nbsp;I'm trying to use a formule like that:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Closing Balance = ( Opening Balance&nbsp;&nbsp; + field1 + field2 )&nbsp;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"..&nbsp;Do you now how I can fix it?&nbsp;RgdsEduardo&nbsp;

ashish_mathur · Answer

Hi,
I have solved a similar question in the attached files.
Hope this helps.

anonymous · Answer

Here is how I handled the this issue.&nbsp;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] &lt;= EndOfPrevMonth
        )
    )
RETURN
    IF(
        ISBLANK(BaseAmount),
        BLANK(),
        BaseAmount + 500
    )&nbsp;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] &lt;= EndOMonth
        )
    )
RETURN
    IF(
        ISBLANK(BaseAmount),
        BLANK(),
        BaseAmount + 500
    )

amitchandak · Answer

eduardomarchesi , If this is recursive calculation like excel, that is not possible.
&nbsp;
If it can be cumulative&nbsp;
&nbsp;
Opening = CALCULATE(SUM(Sales[Amount]),filter(all('Date'),'Date'[date] &lt;Min ('Date'[date])))
&nbsp;
Closing =&nbsp; CALCULATE(SUM(Sales[Amount]),filter(all('Date'),'Date'[date] &lt;= Max('Date'[date])))
&nbsp;
Or you can consider
openingbalancemonth, openingbalancequarter, openingbalanceyear: https://youtu.be/6lzYOXI5wfoPower BI Allocating Targets- closingbalancemonth, closingbalancequarter, closingbalanceyear:https://youtu.be/yPQ9UV37LOU

eduardomarchesi · Answer

Hi&nbsp;amitchandak,&nbsp;Thank you very much for your help!!&nbsp;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:&nbsp;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!!&nbsp;RgdsEduardo&nbsp;&nbsp;&nbsp;

anonymous · Answer

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.

Forum Discussion

Calculating Opening / Closing Balance

9 Replies

Related content

Stock opening closing calculation

Dax Queries for Open Close stock

opening closing

Inventory opening closing

Opening and closing balance calculations