Forum Discussion

akhilduvvuru's avatar
akhilduvvuru
Icon for Helper IV rankHelper IV
1 year ago
Solved

Opening and Closing balance recursive calculation

Team, I have a requirement where I have table with 3 fields 1. Value Date, 2. Actual Amount, 3. Flow Amount as mentioned below coming from backend Date  Actual Amount   Flow Amount  18/12/24...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    1 year ago

    Try it like this.

    Fcst Amt = 
VAR _LastActual =
    CALCULATE (
        LASTNONBLANK ( 'Table'[Date], CALCULATE ( SUM ( 'Table'[Actual] ) ) ),
        ALL ( 'Table' )
    )
VAR _LastActualAmt =
    CALCULATE (
        LASTNONBLANKVALUE ( 'Table'[Date], CALCULATE ( SUM ( 'Table'[Actual] ) ) ),
        ALL ( 'Table' )
    )
VAR _RowDate = 'Table'[Date]
RETURN
    IF (
        _RowDate <= _LastActual,
        'Table'[Actual],
        _LastActualAmt
            + CALCULATE (
                SUM ( 'Table'[Flow] ),
                ALL ( 'Table' ),
                'Table'[Date] > _LastActual,
                'Table'[Date] <= _RowDate
            )
    )

     

     

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
1 year ago

Hi akhilduvvuru  -  you need to use DAX with recursive logic. Unfortunately, DAX does not support direct recursion within calculated columns.

 

create  a calculated column as below:

Fcst Amt =
VAR CurrentDate = 'Table'[Date]
VAR CurrentFlow = 'Table'[Flow Amount]
VAR PreviousDate =
CALCULATE(
MAX('Table'[Date]),
FILTER(
'Table',
'Table'[Date] < CurrentDate
)
)
VAR PreviousFcstAmt =
CALCULATE(
MAX('Table'[Fcst Amt]),
FILTER(
'Table',
'Table'[Date] = PreviousDate
)
)
VAR PreviousActualAmt =
CALCULATE(
MAX('Table'[Actual Amount]),
FILTER(
'Table',
'Table'[Date] = PreviousDate
)
)
RETURN
IF(
ISBLANK('Table'[Actual Amount]),
COALESCE(PreviousFcstAmt, PreviousActualAmt) + CurrentFlow,
'Table'[Actual Amount]
)

 

If Actual Amount is blank for the current row, it calculates the Fcst Amt by adding the flow amount to the previous Fcst Amt. Otherwise, it uses the Actual Amount as the starting point.

Can you please check the above logic