topic Re: find next value using previous value in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/find-next-value-using-previous-value/m-p/3160188#M113458 <P>Thankyou so much&nbsp;<span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Wed, 29 Mar 2023 07:45:34 GMT snaraya 2023-03-29T07:45:34Z find next value using previous value https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/find-next-value-using-previous-value/m-p/3159920#M113441 <P>Hi All,</P><P>&nbsp;</P><P>i have below table, my output should be a column which uses</P><P><STRONG>Target previous row-(Target previous row*</STRONG><STRONG>0.3863636363)</STRONG></P><P>&nbsp;</P><P>eg: 2019= 2019</P><P>2020= Target2019-(Target 2019*0.3863636363)</P><P>2021= Traget 2020-(Target 2020*0.3863636363)</P><P>&nbsp;</P><P>&nbsp;</P><P>i tried using below DAX formula but the value is only correct for 2020, as its using last nonblank value of year 2019 for finding growth_Target all the years which is not correct&nbsp;</P><DIV>&nbsp;</DIV><P>&nbsp;</P><LI-CODE lang="c">growth_Target = IF(ISBLANK('Table'[Target]), VAR _curRank = 'Table'[Index] VAR _lastNonBlankRank = MAXX(FILTER('Table', 'Table'[Index]&lt; _curRank &amp;&amp; NOT ISBLANK('Table'[Target]) ), 'Table'[Index]) VAR _lastNonBlankValue = LOOKUPVALUE('Table'[Target], 'Table'[Index], _lastNonBlankRank) RETURN _lastNonBlankValue-( _lastNonBlankValue * POWER(0.3863636363, _curRank - _lastNonBlankRank)), 'Table'[Target])</LI-CODE><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Index</TD><TD>Year</TD><TD>bu</TD><TD>Target</TD><TD>growth_Target</TD></TR><TR><TD>0</TD><TD>2019</TD><TD>a</TD><TD>19.791</TD><TD>19.79096042</TD></TR><TR><TD>1</TD><TD>2020</TD><TD>a</TD><TD>&nbsp;</TD><TD>12.14445299</TD></TR><TR><TD>2</TD><TD>2021</TD><TD>a</TD><TD>&nbsp;</TD><TD>16.836628</TD></TR><TR><TD>3</TD><TD>2022</TD><TD>a</TD><TD>&nbsp;</TD><TD>18.6495138</TD></TR><TR><TD>4</TD><TD>2023</TD><TD>a</TD><TD>&nbsp;</TD><TD>19.34994695</TD></TR><TR><TD>5</TD><TD>2024</TD><TD>a</TD><TD>&nbsp;</TD><TD>19.62056885</TD></TR><TR><TD>6</TD><TD>2025</TD><TD>a</TD><TD>&nbsp;</TD><TD>19.72512731</TD></TR><TR><TD>7</TD><TD>2026</TD><TD>a</TD><TD>&nbsp;</TD><TD>19.7655249</TD></TR><TR><TD>8</TD><TD>2027</TD><TD>a</TD><TD>&nbsp;</TD><TD>19.78113306</TD></TR><TR><TD>9</TD><TD>2028</TD><TD>a</TD><TD>&nbsp;</TD><TD>19.78716348</TD></TR><TR><TD>10</TD><TD>2029</TD><TD>a</TD><TD>&nbsp;</TD><TD>19.78949342</TD></TR><TR><TD>11</TD><TD>2030</TD><TD>a</TD><TD>&nbsp;</TD><TD>19.79039362</TD></TR><TR><TD>12</TD><TD>2019</TD><TD>b</TD><TD>3.177</TD><TD>3.176699292</TD></TR><TR><TD>13</TD><TD>2020</TD><TD>b</TD><TD>&nbsp;</TD><TD>1.949338202</TD></TR><TR><TD>14</TD><TD>2021</TD><TD>b</TD><TD>&nbsp;</TD><TD>2.702491598</TD></TR><TR><TD>15</TD><TD>2022</TD><TD>b</TD><TD>&nbsp;</TD><TD>2.993482683</TD></TR><TR><TD>16</TD><TD>2023</TD><TD>b</TD><TD>&nbsp;</TD><TD>3.105911056</TD></TR><TR><TD>17</TD><TD>2024</TD><TD>b</TD><TD>&nbsp;</TD><TD>3.149349292</TD></TR><TR><TD>18</TD><TD>2025</TD><TD>b</TD><TD>&nbsp;</TD><TD>3.166132246</TD></TR><TR><TD>19</TD><TD>2026</TD><TD>b</TD><TD>&nbsp;</TD><TD>3.17261657</TD></TR><TR><TD>20</TD><TD>2027</TD><TD>b</TD><TD>&nbsp;</TD><TD>3.175121876</TD></TR><TR><TD>21</TD><TD>2028</TD><TD>b</TD><TD>&nbsp;</TD><TD>3.176089836</TD></TR><TR><TD>22</TD><TD>2029</TD><TD>b</TD><TD>&nbsp;</TD><TD>3.17646382</TD></TR><TR><TD>23</TD><TD>2030</TD><TD>b</TD><TD>&nbsp;</TD><TD>3.176608314</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>can someone help what exactly is wrong&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P> Wed, 29 Mar 2023 05:23:55 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/find-next-value-using-previous-value/m-p/3159920#M113441 snaraya 2023-03-29T05:23:55Z Re: find next value using previous value https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/find-next-value-using-previous-value/m-p/3160063#M113446 <DIV> <DIV><SPAN>growth_Target = </SPAN></DIV> <DIV><SPAN>IF</SPAN><SPAN>(</SPAN><SPAN>ISBLANK</SPAN><SPAN>(</SPAN><SPAN>TestData</SPAN><SPAN>[Target]</SPAN><SPAN>),</SPAN></DIV> <DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>VAR</SPAN> <SPAN>_curRank</SPAN><SPAN> = </SPAN><SPAN>TestData</SPAN><SPAN>[Index]</SPAN></DIV> <DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>VAR</SPAN> <SPAN>_lastNonBlankRank</SPAN><SPAN> = </SPAN><SPAN>MAXX</SPAN><SPAN>(</SPAN><SPAN>FILTER</SPAN><SPAN>(</SPAN><SPAN>TestData</SPAN><SPAN>, </SPAN><SPAN>TestData</SPAN><SPAN>[Index]</SPAN><SPAN>&lt; </SPAN><SPAN>_curRank</SPAN><SPAN> &amp;&amp; </SPAN><SPAN>NOT</SPAN> <SPAN>ISBLANK</SPAN><SPAN>(</SPAN><SPAN>TestData</SPAN><SPAN>[Target]</SPAN><SPAN>) ), </SPAN><SPAN>TestData</SPAN><SPAN>[Index]</SPAN><SPAN>)</SPAN></DIV> <DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>VAR</SPAN> <SPAN>_lastNonBlankValue</SPAN><SPAN> = </SPAN><SPAN>LOOKUPVALUE</SPAN><SPAN>(</SPAN><SPAN>TestData</SPAN><SPAN>[Target]</SPAN><SPAN>, </SPAN><SPAN>TestData</SPAN><SPAN>[Index]</SPAN><SPAN>, </SPAN><SPAN>_lastNonBlankRank</SPAN><SPAN>)</SPAN></DIV> <DIV><SPAN>&nbsp; &nbsp;</SPAN><SPAN>RETURN</SPAN></DIV> <DIV><SPAN>_lastNonBlankValue</SPAN><SPAN> * </SPAN><SPAN>POWER</SPAN><SPAN>(</SPAN><SPAN>1</SPAN><SPAN>-</SPAN><SPAN>0.3863636363</SPAN><SPAN>,</SPAN><SPAN>_curRank</SPAN><SPAN> - </SPAN><SPAN>_lastNonBlankRank</SPAN><SPAN>), </SPAN></DIV> <DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>TestData</SPAN><SPAN>[Target]</SPAN><SPAN>)</SPAN></DIV> </DIV> Wed, 29 Mar 2023 06:45:18 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/find-next-value-using-previous-value/m-p/3160063#M113446 wdx223_Daniel 2023-03-29T06:45:18Z Re: find next value using previous value https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/find-next-value-using-previous-value/m-p/3160188#M113458 <P>Thankyou so much&nbsp;<span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Wed, 29 Mar 2023 07:45:34 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/find-next-value-using-previous-value/m-p/3160188#M113458 snaraya 2023-03-29T07:45:34Z