Recursive Dax Calculation

[AdamPBIDev]

Dear Dax Gurus,

Would appreciate some help - I'm looking to create a calculated column forecast which at index 0 simply takes the (Activity + (Activity * Activity Change %)), but for all other indexes takes the previous row calculated forecast and applies the current row activity change %. 

Note the index resets at each change in (Cost Centre)"Code".

Screenshot 1

Sample Data

Month Year	Code	Activity Change %	Activity	Index	Forecast		Formula
01/03/2022	100	2.0%	3048	0	3108		=D2+(D2*C2)
01/04/2022	100	-4.0%		1	2984		=F2+(F2*C3)
01/05/2022	100	5.0%		2	3134
01/06/2022	100	7.0%		3	3353
01/07/2022	100	-4.0%		4	3219
01/08/2022	100	8.0%		5	3476
01/09/2022	100	-3.0%		6	3371
01/10/2022	100	2.0%		7	3439
01/11/2022	100	4.0%		8	3576
01/12/2022	100	-6.0%		9	3363
01/03/2022	200	1.7%	3448	0	3506		=D12+(D12*C12)
01/04/2022	200	-3.4%		1	3387		=F12+(F12*C13)
01/05/2022	200	4.2%		2	3530
01/06/2022	200	5.9%		3	3739
01/07/2022	200	-3.4%		4	3611
01/08/2022	200	6.8%		5	3857
01/09/2022	200	-2.6%		6	3757
01/10/2022	200	1.7%		7	3822
01/11/2022	200	3.4%		8	3952
01/12/2022	200	-5.2%		9	3749

I did try Gerhard Brueckl's interesting approach for creating this entirely in DAX Link  but unfortunately I couldn't get this to work at Cost Centre Code level (works perfectly at an overall level) so have gone down the path of creating the above table instead

Thanks in advance

Adam

[AntrikshSharma]

@AdamPBIDev @regnig This should be done in PQ as List.Accumulate allows for easy recursion.

let
    Source = Table,
    Group = Table.Group (
        Source,
        { "Code" },
        {
            {
                "All",
                each
                    let
                        Source = _,
                        SortByIndex = Table.Sort ( Source, { { "Index", Order.Ascending } } ),
                        RemovedOtherColumns = Table.SelectColumns (
                            SortByIndex,
                            { "Activity Change %", "Activity" }
                        ),
                        ToRows = Table.ToRows ( RemovedOtherColumns ),
                        Transform = List.Accumulate (
                            ToRows,
                            {},
                            ( s, c ) =>
                                let
                                    a = if c{1} = null then List.Last ( s ) else c{1},
                                    b = s & { a + ( c{0} * a ) }
                                in
                                    b
                        ),
                        JoinColumns = Table.FromColumns (
                            Table.ToColumns ( Source ) & { Transform },
                            Table.ColumnNames ( Source ) & { "Forecast" }
                        )
                    in
                        JoinColumns
            }
        }
    )[All],
    Combine = Table.Combine ( Group ),
    ChangedType = Table.TransformColumnTypes ( Combine, { { "Forecast", Currency.Type } } )
in
    ChangedType

PBIX is attached below.

[regnig]

Can we used DAX instead of PQ? I use DAX table to build the data table which mean I cannot use PQ. here my lates Calculated Column.

Value2 =

Var init =

MAXX(

    filter(

        Sheet1,

        Sheet1[Date] = DATE(2025,1,2)

    ),

    [Activity] + Sheet1[Value]

)

Var o =

PRODUCTX(

    Filter(

        Sheet1,

        Sheet1[Date]  <= EARLIER(Sheet1[Date])

    ),

    (IF(

        Sheet1[Date] = MINX(Sheet1, Sheet1[Date]),

        init,

        1 + Sheet1[Value]

    ) * Sheet1[Percetage])

)

return o

but the result return (F2+F2*[Value])*[Percentage]. I expecting to get (F2+[Value])*[Percentage] actualy.

Thank you.

[AntrikshSharma]

@regnig no, while iteration, temporarily computed values of the previous rows aren't available in DAX and the calculation depends on the previous row during current row. You can import the table create in DAX by using PQ and then use the M code.

[v-shex-msft · Accepted solution]

Hi @AdamPBIDev,

AFAIK, current power bi data model tables do not include row and column index AND DAX expression does not support doing recursive calculations.

Previous Value (“Recursion”) in DAX - Microsoft Power BI Community

You can enter to query editor to do these calculations but it will be poor performance on recursive calculations.

Recursive Functions in Power BI / Power Query — The Power User
Regards,

Xiaoxin Sheng

[amitchandak]

@AdamPBIDev , Try a new column,

Value = MAXX(filter(Data, [Index] =0 && [Code] =EARLIER(Data[Code])),[Activity])* PRODUCTX(Filter(DATA, [Code] =EARLIER(Data[Code]) && [Index]  <=EARLIER(Data[Index])), 1+[Activity Change %])

File attached after signature

[regnig]

Hi, @amitchandak 

I'm facing similar problem with recursive.

lets say i have an additional column called Cons. Also the formula is described bellow.

F2=C2*(D2 + Cons)

F3=D2*(F2 + Cons)

I've been struggling for that formula. would you like to give some advice. Thank you