topic Dax Recursive Calculated Column in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dax-Recursive-Calculated-Column/m-p/2377354#M61318 <P>Looking for a bit of help with a recursive forecasting challenge.&nbsp;</P><P>&nbsp;</P><P>Effectively I'd like to create a calculated column which for the first index takes the Activity + (&nbsp;Activity * Activity Change %), but for other rows takes the prior calculation and applies the current row activity change. This occurs on multiple cost centre levels (code).</P><P>&nbsp;</P><P><STRONG>Screenshot</STRONG></P><P><img /></P><P>&nbsp;</P><P><STRONG>Data</STRONG></P><P>&nbsp;</P><TABLE><TBODY><TR><TD><P>Month Year</P></TD><TD><P>Code</P></TD><TD><P>Activity Change %</P></TD><TD><P>Activity</P></TD><TD><P>Index</P></TD><TD><P>Forecast</P></TD><TD><P>&nbsp;</P></TD><TD><P>Formula</P></TD></TR><TR><TD><P>01/03/2022</P></TD><TD><P>100</P></TD><TD><P>2.0%</P></TD><TD><P>3048</P></TD><TD><P>0</P></TD><TD><P>3108</P></TD><TD><P>&nbsp;</P></TD><TD><P>=D2+(D2*C2)</P></TD></TR><TR><TD><P>01/04/2022</P></TD><TD><P>100</P></TD><TD><P>-4.0%</P></TD><TD><P>&nbsp;</P></TD><TD><P>1</P></TD><TD><P>2984</P></TD><TD><P>&nbsp;</P></TD><TD><P>=F2+(F2*C3)</P></TD></TR><TR><TD><P>01/05/2022</P></TD><TD><P>100</P></TD><TD><P>5.0%</P></TD><TD><P>&nbsp;</P></TD><TD><P>2</P></TD><TD><P>3134</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/06/2022</P></TD><TD><P>100</P></TD><TD><P>7.0%</P></TD><TD><P>&nbsp;</P></TD><TD><P>3</P></TD><TD><P>3353</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/07/2022</P></TD><TD><P>100</P></TD><TD><P>-4.0%</P></TD><TD><P>&nbsp;</P></TD><TD><P>4</P></TD><TD><P>3219</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/08/2022</P></TD><TD><P>100</P></TD><TD><P>8.0%</P></TD><TD><P>&nbsp;</P></TD><TD><P>5</P></TD><TD><P>3476</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/09/2022</P></TD><TD><P>100</P></TD><TD><P>-3.0%</P></TD><TD><P>&nbsp;</P></TD><TD><P>6</P></TD><TD><P>3371</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/10/2022</P></TD><TD><P>100</P></TD><TD><P>2.0%</P></TD><TD><P>&nbsp;</P></TD><TD><P>7</P></TD><TD><P>3439</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/11/2022</P></TD><TD><P>100</P></TD><TD><P>4.0%</P></TD><TD><P>&nbsp;</P></TD><TD><P>8</P></TD><TD><P>3576</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/12/2022</P></TD><TD><P>100</P></TD><TD><P>-6.0%</P></TD><TD><P>&nbsp;</P></TD><TD><P>9</P></TD><TD><P>3363</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/03/2022</P></TD><TD><P>200</P></TD><TD><P>1.7%</P></TD><TD><P>3448</P></TD><TD><P>0</P></TD><TD><P>3506</P></TD><TD><P>&nbsp;</P></TD><TD><P>=D12+(D12*C12)</P></TD></TR><TR><TD><P>01/04/2022</P></TD><TD><P>200</P></TD><TD><P>-3.4%</P></TD><TD><P>&nbsp;</P></TD><TD><P>1</P></TD><TD><P>3387</P></TD><TD><P>&nbsp;</P></TD><TD><P>=F12+(F12*C13)</P></TD></TR><TR><TD><P>01/05/2022</P></TD><TD><P>200</P></TD><TD><P>4.2%</P></TD><TD><P>&nbsp;</P></TD><TD><P>2</P></TD><TD><P>3530</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/06/2022</P></TD><TD><P>200</P></TD><TD><P>5.9%</P></TD><TD><P>&nbsp;</P></TD><TD><P>3</P></TD><TD><P>3739</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/07/2022</P></TD><TD><P>200</P></TD><TD><P>-3.4%</P></TD><TD><P>&nbsp;</P></TD><TD><P>4</P></TD><TD><P>3611</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/08/2022</P></TD><TD><P>200</P></TD><TD><P>6.8%</P></TD><TD><P>&nbsp;</P></TD><TD><P>5</P></TD><TD><P>3857</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/09/2022</P></TD><TD><P>200</P></TD><TD><P>-2.6%</P></TD><TD><P>&nbsp;</P></TD><TD><P>6</P></TD><TD><P>3757</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/10/2022</P></TD><TD><P>200</P></TD><TD><P>1.7%</P></TD><TD><P>&nbsp;</P></TD><TD><P>7</P></TD><TD><P>3822</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/11/2022</P></TD><TD><P>200</P></TD><TD><P>3.4%</P></TD><TD><P>&nbsp;</P></TD><TD><P>8</P></TD><TD><P>3952</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/12/2022</P></TD><TD><P>200</P></TD><TD><P>-5.2%</P></TD><TD><P>&nbsp;</P></TD><TD><P>9</P></TD><TD><P>3749</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>I did try&nbsp;Gerhard Brueckl's interesting approach for creating this entirely in DAX&nbsp;<A href="https://blog.gbrueckl.at/2015/04/recursive-calculations-powerpivot-dax/" target="_blank" rel="noopener">Link</A>&nbsp; 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</P><P>&nbsp;</P><P>Thanks in advance</P><P>Adam</P> Sun, 06 Mar 2022 23:37:37 GMT AdamPBIDev 2022-03-06T23:37:37Z Dax Recursive Calculated Column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dax-Recursive-Calculated-Column/m-p/2377354#M61318 <P>Looking for a bit of help with a recursive forecasting challenge.&nbsp;</P><P>&nbsp;</P><P>Effectively I'd like to create a calculated column which for the first index takes the Activity + (&nbsp;Activity * Activity Change %), but for other rows takes the prior calculation and applies the current row activity change. This occurs on multiple cost centre levels (code).</P><P>&nbsp;</P><P><STRONG>Screenshot</STRONG></P><P><img /></P><P>&nbsp;</P><P><STRONG>Data</STRONG></P><P>&nbsp;</P><TABLE><TBODY><TR><TD><P>Month Year</P></TD><TD><P>Code</P></TD><TD><P>Activity Change %</P></TD><TD><P>Activity</P></TD><TD><P>Index</P></TD><TD><P>Forecast</P></TD><TD><P>&nbsp;</P></TD><TD><P>Formula</P></TD></TR><TR><TD><P>01/03/2022</P></TD><TD><P>100</P></TD><TD><P>2.0%</P></TD><TD><P>3048</P></TD><TD><P>0</P></TD><TD><P>3108</P></TD><TD><P>&nbsp;</P></TD><TD><P>=D2+(D2*C2)</P></TD></TR><TR><TD><P>01/04/2022</P></TD><TD><P>100</P></TD><TD><P>-4.0%</P></TD><TD><P>&nbsp;</P></TD><TD><P>1</P></TD><TD><P>2984</P></TD><TD><P>&nbsp;</P></TD><TD><P>=F2+(F2*C3)</P></TD></TR><TR><TD><P>01/05/2022</P></TD><TD><P>100</P></TD><TD><P>5.0%</P></TD><TD><P>&nbsp;</P></TD><TD><P>2</P></TD><TD><P>3134</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/06/2022</P></TD><TD><P>100</P></TD><TD><P>7.0%</P></TD><TD><P>&nbsp;</P></TD><TD><P>3</P></TD><TD><P>3353</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/07/2022</P></TD><TD><P>100</P></TD><TD><P>-4.0%</P></TD><TD><P>&nbsp;</P></TD><TD><P>4</P></TD><TD><P>3219</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/08/2022</P></TD><TD><P>100</P></TD><TD><P>8.0%</P></TD><TD><P>&nbsp;</P></TD><TD><P>5</P></TD><TD><P>3476</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/09/2022</P></TD><TD><P>100</P></TD><TD><P>-3.0%</P></TD><TD><P>&nbsp;</P></TD><TD><P>6</P></TD><TD><P>3371</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/10/2022</P></TD><TD><P>100</P></TD><TD><P>2.0%</P></TD><TD><P>&nbsp;</P></TD><TD><P>7</P></TD><TD><P>3439</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/11/2022</P></TD><TD><P>100</P></TD><TD><P>4.0%</P></TD><TD><P>&nbsp;</P></TD><TD><P>8</P></TD><TD><P>3576</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/12/2022</P></TD><TD><P>100</P></TD><TD><P>-6.0%</P></TD><TD><P>&nbsp;</P></TD><TD><P>9</P></TD><TD><P>3363</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/03/2022</P></TD><TD><P>200</P></TD><TD><P>1.7%</P></TD><TD><P>3448</P></TD><TD><P>0</P></TD><TD><P>3506</P></TD><TD><P>&nbsp;</P></TD><TD><P>=D12+(D12*C12)</P></TD></TR><TR><TD><P>01/04/2022</P></TD><TD><P>200</P></TD><TD><P>-3.4%</P></TD><TD><P>&nbsp;</P></TD><TD><P>1</P></TD><TD><P>3387</P></TD><TD><P>&nbsp;</P></TD><TD><P>=F12+(F12*C13)</P></TD></TR><TR><TD><P>01/05/2022</P></TD><TD><P>200</P></TD><TD><P>4.2%</P></TD><TD><P>&nbsp;</P></TD><TD><P>2</P></TD><TD><P>3530</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/06/2022</P></TD><TD><P>200</P></TD><TD><P>5.9%</P></TD><TD><P>&nbsp;</P></TD><TD><P>3</P></TD><TD><P>3739</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/07/2022</P></TD><TD><P>200</P></TD><TD><P>-3.4%</P></TD><TD><P>&nbsp;</P></TD><TD><P>4</P></TD><TD><P>3611</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/08/2022</P></TD><TD><P>200</P></TD><TD><P>6.8%</P></TD><TD><P>&nbsp;</P></TD><TD><P>5</P></TD><TD><P>3857</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/09/2022</P></TD><TD><P>200</P></TD><TD><P>-2.6%</P></TD><TD><P>&nbsp;</P></TD><TD><P>6</P></TD><TD><P>3757</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/10/2022</P></TD><TD><P>200</P></TD><TD><P>1.7%</P></TD><TD><P>&nbsp;</P></TD><TD><P>7</P></TD><TD><P>3822</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/11/2022</P></TD><TD><P>200</P></TD><TD><P>3.4%</P></TD><TD><P>&nbsp;</P></TD><TD><P>8</P></TD><TD><P>3952</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>01/12/2022</P></TD><TD><P>200</P></TD><TD><P>-5.2%</P></TD><TD><P>&nbsp;</P></TD><TD><P>9</P></TD><TD><P>3749</P></TD><TD><P>&nbsp;</P></TD><TD><P>&nbsp;</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>I did try&nbsp;Gerhard Brueckl's interesting approach for creating this entirely in DAX&nbsp;<A href="https://blog.gbrueckl.at/2015/04/recursive-calculations-powerpivot-dax/" target="_blank" rel="noopener">Link</A>&nbsp; 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</P><P>&nbsp;</P><P>Thanks in advance</P><P>Adam</P> Sun, 06 Mar 2022 23:37:37 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dax-Recursive-Calculated-Column/m-p/2377354#M61318 AdamPBIDev 2022-03-06T23:37:37Z Re: Dax Recursive Calculated Column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dax-Recursive-Calculated-Column/m-p/2378567#M61381 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="364339" data-lia-user-login="AdamPBIDev" class="lia-mention lia-mention-user">AdamPBIDev</a> , Try like</P> <P>&nbsp;</P> <P>maxx(filter(Table, [Code] = earlier([Code])), [Activity]) * ProductX(filter(Table, [Code] = earlier([Code])), 1+[Activity Change %])</P> Mon, 07 Mar 2022 12:15:01 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dax-Recursive-Calculated-Column/m-p/2378567#M61381 amitchandak 2022-03-07T12:15:01Z Re: Dax Recursive Calculated Column https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dax-Recursive-Calculated-Column/m-p/2379658#M61451 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="148838" data-lia-user-login="amitchandak" class="lia-mention lia-mention-user">amitchandak</a>&nbsp; Thanks for your suggestion!&nbsp;&nbsp;</P><P>Interestingly it returns the correct closing position forecast number but repeats the value each month rather than recalculating for each position - Screenshot attached</P><P>&nbsp;</P><P>&nbsp;</P><P><img /></P><P>Any ideas?</P><P>&nbsp;</P><P>Cheers</P><P>Adam</P> Mon, 07 Mar 2022 21:48:00 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dax-Recursive-Calculated-Column/m-p/2379658#M61451 AdamPBIDev 2022-03-07T21:48:00Z