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                          4,32,10,037
17/12/24                          3,18,83,300
16/12/24                          1,69,62,383
15/12/24                          8,07,60,256
14/12/24                       11,23,63,399
13/12/24                       17,53,85,676
12/12/24                       55,23,36,526
11/12/24                          1,21,72,840
10/12/24         (37,08,72,079)                         4,74,84,330
09/12/24         (37,08,60,843)                         2,18,93,319
08/12/24         (37,07,90,934)                         5,70,65,836
07/12/24         (37,07,90,934)                         2,16,90,139
06/12/24         (37,07,90,934)                         5,38,41,586
05/12/24         (37,07,90,934)                      17,81,70,184
04/12/24         (37,07,90,689)                         8,56,08,933
03/12/24         (37,07,90,689)                 1,17,72,83,663

 

Now the requirement is to create Fcst Amt column from where the Actual Amount ends (i.e., 10/12/24). Next day ( i.e.,11/12/24) onwards, we need to recuresively calculate by adding the flow amount.

Examples:

On 11/12/24, we should take Prev day Actual Amount -37,08,72,078 + 11/12/24 Flow Amount 1,21,72,840 = -35,86,99,238

On 12/12/24, we should take Prev day Fcst Amt -35,86,99,238 + 12/12/24 Flow Amount 55,23,36,525 = 19,26,37,287

On 13/12/24, we should take Prev day Fcst Amt 19,26,37,287 + 13/12/24 Flow Amount 17,53,85,676 = 36,90,22,964

 

Expected Output: Fcst Amt

I want Fcst Amt as Calculated Column but not the Measure because I want to create Opening Bal and Closing Bal measures on Fcst Amt field.

Stuck with the same. Please someone help

Thanks!

 

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

     

     

5 Replies

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

         

         

  • 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