topic Recursive Dax Calculation in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Recursive-Dax-Calculation/m-p/2377334#M61316 <P>Dear Dax Gurus,</P><P>&nbsp;</P><P>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 %.&nbsp;</P><P>Note the index resets at each change in (Cost Centre)"Code".</P><P>&nbsp;</P><P><STRONG>Screenshot 1</STRONG></P><P><img /></P><P>&nbsp;</P><P><STRONG>Sample 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><P>&nbsp;</P> Sun, 06 Mar 2022 22:23:44 GMT AdamPBIDev 2022-03-06T22:23:44Z Recursive Dax Calculation https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Recursive-Dax-Calculation/m-p/2377334#M61316 <P>Dear Dax Gurus,</P><P>&nbsp;</P><P>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 %.&nbsp;</P><P>Note the index resets at each change in (Cost Centre)"Code".</P><P>&nbsp;</P><P><STRONG>Screenshot 1</STRONG></P><P><img /></P><P>&nbsp;</P><P><STRONG>Sample 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><P>&nbsp;</P> Sun, 06 Mar 2022 22:23:44 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Recursive-Dax-Calculation/m-p/2377334#M61316 AdamPBIDev 2022-03-06T22:23:44Z Re: Recursive Dax Calculation https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Recursive-Dax-Calculation/m-p/2380614#M61493 <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>&nbsp;</P> <P>Try a new column,</P> <P>&nbsp;</P> <P>&nbsp;</P> <LI-CODE lang="markup">Value = MAXX(filter(Data, [Index] =0 &amp;&amp; [Code] =EARLIER(Data[Code])),[Activity])* PRODUCTX(Filter(DATA, [Code] =EARLIER(Data[Code]) &amp;&amp; [Index] &lt;=EARLIER(Data[Index])), 1+[Activity Change %])</LI-CODE> <P>&nbsp;</P> <P>&nbsp;</P> <P>File attached after signature</P> Tue, 08 Mar 2022 07:55:18 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Recursive-Dax-Calculation/m-p/2380614#M61493 amitchandak 2022-03-08T07:55:18Z