Use ending balance of previous row in next row for calculation

Question

I need to calculate the opening balance and ending balance based on date. Here are three main measures involved.

OpeningBalance For 1st day, the initial amount is coming from another table. I have a measure for each year that fetches the value based on year selected. For the rest of the dates, the value of EndingBalance of previous date is the opening balance of next date.

Sales This column contains sales amount.

EndingBalance MonthlyEndingBalance = OpeningBalance + MonthSales

Example.
For 1st day

OpeningBalance = 10
Sales = 15
EndingBalance  (OpeningBalance+Sales) = 25

For next day

OpeningBalance = 25 (EndingBalance of previous row)
Sales = 20
EndingBalance  (EndingBalance of previous row +Sales) = 45

repeat same logic until the last available date.

Here is a simple example for understanding.

Simple Example.

Here is the PBIX file with dummy data. Logic is same but it tells the whole story . Download PBIX here

Thanks in advance

Opening Balance Adjusted =
VAR _SelectedMonth = SELECTEDVALUE(Activitylist[FX_MonthNumber])
VAR _SelectedYear = SELECTEDVALUE(Activitylist[FX_Year])

RETURN
IF(
MAX(Activitylist[IsFirstMonth]) = 1 && _SelectedYear = SELECTEDVALUE(Activitylist[FX_Year]),
[OB Initial Column Measure],  // Use OB2024 measure for the first month

CALCULATE(
    [Closing Balance Measure],

    FILTER(
        ALL('Activitylist'),
        'Activitylist'[FX_MonthNumber] = _SelectedMonth - 1 &&
        'Activitylist'[FX_Year] = _SelectedYear &&
        'Activitylist'[LoB] = SELECTEDVALUE(Activitylist[LoB])
    )
)

)

Closing Balance Measure =
VAR _SelectedMonth = SELECTEDVALUE(Activitylist[FX_MonthNumber])
VAR _SelectedYear = SELECTEDVALUE(Activitylist[FX_Year])
VAR _OBInitial = SELECTEDVALUE(Activitylist[OBInitialColumn])
VAR _OBdjusted = SELECTEDVALUE(Activitylist[OBAdjustedColumn])

RETURN
IF(
MIN(Activitylist[IsFirstMonth]) = 1 && SELECTEDVALUE(Activitylist[FX_Year]) = _SelectedYear,
_OBInitial + [Monthly Variation],  // For the first month
CALCULATE(
ROUND( [OB Initial] + [Monthly Variation],0)
// here is the issue. instead of [OB Initial] , [Opening Balance Adjusted] should be here but circular dependency error occurs
)

Answer 1

You should consider using relationships and adopting a Star Schema model.

With your given model, try these new measures, create them in this order:

* Sales = 
  CALCULATE(
    SUM(Activitylist[FX_Net Contract Revenue Increase USD]),
    NOT(ISBLANK(Activitylist[Actual Opp sale type])) &&
    Activitylist[Actual Opp sale type] <> "New"
  )

* Sales YTD = 
  TOTALYTD(
    [* Sales],
    Activitylist[Actual signed date],
    ALLSELECTED(Activitylist)
  )

* Start of Year Balance = 
  CALCULATE(
    MAX(OB[ACVSUMUSD]),
    FILTER(OB, [LoB] = MAX(Activitylist[LoB]) && [SignedYear] = MAX(Activitylist[FX_Year]) )
  )

* Closing Balance = [* Start of Year Balance] + [* Sales YTD]

* Opening Balance = [* Closing Balance] - [* Sales]

The above should work for Week & Quarter as well.