- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
If this post helps, please consider accept as solution to help other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
If this post helps, please consider accept as solution to help other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
10-10-2023 01:51 AM | |||
09-16-2024 02:08 PM | |||
09-30-2024 08:43 PM | |||
03-25-2024 07:57 AM | |||
05-14-2024 07:56 AM |
User | Count |
---|---|
25 | |
15 | |
11 | |
8 | |
8 |
User | Count |
---|---|
19 | |
15 | |
15 | |
14 | |
13 |