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

[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