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