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
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.