topic Recursive Dax Calculation in DAX Commands and Tips

Recursive Dax Calculation

AdamPBIDev — Sun, 06 Mar 2022 22:18:25 GMT

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

Re: Recursive Dax Calculation

amitchandak — Tue, 08 Mar 2022 07:54:54 GMT

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

Re: Recursive Dax Calculation

Anonymous — Fri, 11 Mar 2022 07:31:14 GMT

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

Re: Recursive Dax Calculation

regnig — Mon, 10 Feb 2025 04:50:06 GMT

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

Re: Recursive Dax Calculation

AntrikshSharma — Mon, 10 Feb 2025 07:15:46 GMT

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.

Re: Recursive Dax Calculation

regnig — Tue, 11 Feb 2025 04:47:46 GMT

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.

Re: Recursive Dax Calculation

AntrikshSharma — Tue, 11 Feb 2025 05:56:16 GMT

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.