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

     

     

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

akhilduvvuru 

I think this is doing what you are looking for based on my sample.

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 (
        NOT ISBLANK ( 'Table'[Actual] ),
        'Table'[Actual],
        _LastActualAmt
            + CALCULATE (
                SUM ( 'Table'[Flow] ),
                ALL ( 'Table' ),
                'Table'[Date] > _LastActual,
                'Table'[Date] <= _RowDate
            )
    )

 

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

    Thanks jdbuchanan71 for your help. Small correction in the way data is present.

     

    This is how the data looks like. Please help with the same.

    Because, my data is in the above format, when I apply the logic which you suggested, I'm getting the following result.

     

     

    • jdbuchanan71's avatar
      jdbuchanan71
      Icon for Super User rankSuper User
      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
            )
    )