Opening and Closing balance recursive calculation

[akhilduvvuru]

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!

[rajendraongole1]

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 

[jdbuchanan71]

@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]

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 · Accepted solution]

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

[akhilduvvuru]

@danextian @Ritaf1983 @rajendraongole1 @Greg_Deckler @Ashish_Mathur @lbendlin @amitchandak @jdbuchanan71 @Anonymous @Anonymous @v-henryk-mstf - Any help here is much appriciated. Thanks!