Forum Discussion
Solved
Recursive Dax Calculation
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 in...
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
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.