topic Re: Use previous measure result in row to calculate the result of the following row in measure in Desktop
https://community.powerbi.com/t5/Desktop/Use-previous-measure-result-in-row-to-calculate-the-result-of/m-p/1137294#M517333
<P><LI-USER uid="233051"></LI-USER> - Right, it's as I feared, you really want a calculation that is based on a previous iteration of that calculation. It's really difficult with DAX because there is no recursion in DAX. You have to essentially brute force it or at least I haven't found a magical way of doing it yet. That article that I pointed you to has my best attempt at it in DAX as well as pointers to other articles where I have struggled with this. You would have better luck doing it in Power Query and I have a series of articles on that where I used recursion in Power Query. Those are linked from that article I included in my previous post. </P>
<P> </P>Wed, 03 Jun 2020 00:22:03 GMTGreg_Deckler2020-06-03T00:22:03ZUse previous measure result in row to calculate the result of the following row in measure
https://community.powerbi.com/t5/Desktop/Use-previous-measure-result-in-row-to-calculate-the-result-of/m-p/1136968#M517222
<P>Hi</P><P> </P><P>I found a major road block in my model. Im trying to normalize my price data. </P><P>I am using the % change based on my index which i already create a measure for.</P><P> </P><P>Starting at 1</P><P>1 +(%change*1)= 0.984</P><P> </P><P>Then I need to use the previous result for all subsequent records</P><P>0.9484 + (%change * 0984) and so forth for all my records</P><P> </P><P>Here is the excel data sample and formula.</P><P> </P><P>I need to be able to use the result from 1 in the same measure to calculate the next version and so on</P><P>=AY2+(AL3*AY2)</P><P>=AY3+(AL3*AY3)</P><P>=AY4+(AL3*AY4)</P><P>and so forth</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Capturef1.PNG" style="width: 945px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/287025i5B58BA91CF58DF84/image-size/large?v=v2&px=999" role="button" title="Capturef1.PNG" alt="Capturef1.PNG" /></span></P><P> </P><P> </P><P>I have tried the following measure but it doesnt work</P><P> </P><P>Trade day is my index column</P><P> </P><DIV><DIV><SPAN>Norm. Price Final F = if(min(Winter_Contracts_Zema[Trade Day]) = 1,1, IF(MIN(Winter_Contracts_Zema[Trade Day]) = 2, 1 + [% Change.P] * 1,0))</SPAN></DIV><DIV> </DIV><DIV><SPAN>That works for index 1 and 2 i get the same values of 1 for all years and of 2 for all years. but how do I use then the value of 2 to calculate 3 and the value of 2 to calculate 4 and so forth?</SPAN></DIV><DIV> </DIV><DIV><SPAN>I highly apppreciate your assitance on this, as I am pretty much stuck on how to use the previous value of the measure for the next value of the measure. </SPAN></DIV><DIV> </DIV><DIV><SPAN>Thanks a lot!</SPAN></DIV><DIV> </DIV><DIV><BR /><DIV class="lia-user-attributes"><DIV class="lia-user-name"><SPAN class="UserName lia-user-name lia-user-rank-Super-User-IV"><SPAN class="login-bold"><LI-USER uid="148838"></LI-USER> </SPAN></SPAN></DIV><DIV class="lia-user-name"><SPAN class="UserName lia-user-name lia-user-rank-Super-User-IV"><SPAN class="login-bold"><LI-USER uid="313"></LI-USER> </SPAN></SPAN></DIV><DIV class="lia-user-name"> </DIV></DIV><P> </P><BR /><P> </P></DIV></DIV>Tue, 02 Jun 2020 19:45:55 GMThttps://community.powerbi.com/t5/Desktop/Use-previous-measure-result-in-row-to-calculate-the-result-of/m-p/1136968#M517222reynags912020-06-02T19:45:55ZRe: Use previous measure result in row to calculate the result of the following row in measure
https://community.powerbi.com/t5/Desktop/Use-previous-measure-result-in-row-to-calculate-the-result-of/m-p/1137152#M517284
<P><LI-USER uid="233051"></LI-USER> - I'm not sure I am following this completely, sample data as text and expected results would help. However, it smells like recursion and if that is the case that is difficult if not impossible in DAX. <A href="https://community.powerbi.com/t5/Quick-Measures-Gallery/Previous-Value-aka-quot-Recursion-quot/m-p/637614#M321" target="_blank">https://community.powerbi.com/t5/Quick-Measures-Gallery/Previous-Value-aka-quot-Recursion-quot/m-p/637614#M321</A></P>Tue, 02 Jun 2020 21:39:57 GMThttps://community.powerbi.com/t5/Desktop/Use-previous-measure-result-in-row-to-calculate-the-result-of/m-p/1137152#M517284Greg_Deckler2020-06-02T21:39:57ZRe: Use previous measure result in row to calculate the result of the following row in measure
https://community.powerbi.com/t5/Desktop/Use-previous-measure-result-in-row-to-calculate-the-result-of/m-p/1137187#M517297
<P>Thanks so much for answering! </P><P> </P><P>Percent change data % in my model is a measure. that shows % day over day (trade day)</P><P><SPAN>Trade Day (index in my model) goes up to 250 for each contract year</SPAN></P><P><SPAN>Contract Year: from 2008 to 2019</SPAN></P><P><SPAN>This is the data</SPAN></P><P><SPAN>Percentage Change % data</SPAN></P><TABLE border="1"><TBODY><TR><TD>Trade Day</TD><TD>2008</TD><TD>2010</TD></TR><TR><TD>1</TD><TD>0</TD><TD>0</TD></TR><TR><TD>2</TD><TD>-0.016<BR /><BR /></TD><TD>0.000<BR /><BR /></TD></TR><TR><TD>3</TD><TD><SPAN>0.014</SPAN></TD><TD>0.010</TD></TR><TR><TD>4</TD><TD><SPAN>0.008</SPAN></TD><TD>0.010</TD></TR><TR><TD>5</TD><TD><SPAN>0.005</SPAN></TD><TD>-0.009</TD></TR><TR><TD>6</TD><TD><SPAN>-0.005</SPAN></TD><TD>0.000</TD></TR><TR><TD>7</TD><TD><SPAN>0.000</SPAN></TD><TD>0.000</TD></TR><TR><TD>8</TD><TD><SPAN>-0.005</SPAN></TD><TD>0.005</TD></TR><TR><TD>9</TD><TD><SPAN>-0.005</SPAN></TD><TD>-0.005</TD></TR><TR><TD>10</TD><TD><SPAN>-0.005</SPAN></TD><TD>-0.005</TD></TR></TBODY></TABLE><P> </P><P>What I'm trying to get is a value that starts at 1 on first index value and perform a calculations based on the result of its previous index.</P><P>Price Normalized: This is the numbers Im trying to achieve per trade day.</P><TABLE border="1"><TBODY><TR><TD>Trade Day</TD><TD>2008</TD><TD>2009</TD></TR><TR><TD>1</TD><TD>1</TD><TD>1</TD></TR><TR><TD>2</TD><TD>0.984</TD><TD>1.000</TD></TR><TR><TD>3</TD><TD>0.997</TD><TD>1.010</TD></TR><TR><TD>4</TD><TD>1.005</TD><TD>1.019</TD></TR><TR><TD>5</TD><TD>1.011</TD><TD>1.010</TD></TR><TR><TD>6</TD><TD>1.005</TD><TD>1.010</TD></TR><TR><TD>7</TD><TD>1.005</TD><TD>1.010</TD></TR><TR><TD>8</TD><TD>1.000</TD><TD>1.014</TD></TR><TR><TD>9</TD><TD>0.995</TD><TD>1.010</TD></TR></TBODY></TABLE><P> </P><P> </P><P>In excel is done by Previous row +(percentage change row * previous row) so 1 + (- 0.016 * 1) = <SPAN>0.984</SPAN></P><P> </P><P>Any possible way to get these numbers? if its impossible in DAX. how can get to this results in another way? maybe calculated columns based on earlier and my % change measure? Creating a table?</P><P> </P><P>Thank so much for your help. I keep going in circles <LI-USER uid="313"></LI-USER> </P><P> </P><P> </P>Tue, 02 Jun 2020 22:11:42 GMThttps://community.powerbi.com/t5/Desktop/Use-previous-measure-result-in-row-to-calculate-the-result-of/m-p/1137187#M517297reynags912020-06-02T22:11:42ZRe: Use previous measure result in row to calculate the result of the following row in measure
https://community.powerbi.com/t5/Desktop/Use-previous-measure-result-in-row-to-calculate-the-result-of/m-p/1137294#M517333
<P><LI-USER uid="233051"></LI-USER> - Right, it's as I feared, you really want a calculation that is based on a previous iteration of that calculation. It's really difficult with DAX because there is no recursion in DAX. You have to essentially brute force it or at least I haven't found a magical way of doing it yet. That article that I pointed you to has my best attempt at it in DAX as well as pointers to other articles where I have struggled with this. You would have better luck doing it in Power Query and I have a series of articles on that where I used recursion in Power Query. Those are linked from that article I included in my previous post. </P>
<P> </P>Wed, 03 Jun 2020 00:22:03 GMThttps://community.powerbi.com/t5/Desktop/Use-previous-measure-result-in-row-to-calculate-the-result-of/m-p/1137294#M517333Greg_Deckler2020-06-03T00:22:03ZRe: Use previous measure result in row to calculate the result of the following row in measure
https://community.powerbi.com/t5/Desktop/Use-previous-measure-result-in-row-to-calculate-the-result-of/m-p/1138425#M517671
<P>Hi <LI-USER uid="233051"></LI-USER> ,</P><P>Based on your description, I have created table like this:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="table.png" style="width: 216px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/287427i6F4DCEFDFD3D0D92/image-size/large?v=v2&px=999" role="button" title="table.png" alt="table.png" /></span></P><P>Simple measure as 2008 and 2009:</P><LI-CODE lang="markup">_2008 = SUM('Percentage Change % Data'[2008])</LI-CODE><LI-CODE lang="markup">_2009 = SUM('Percentage Change % Data'[2009])</LI-CODE><P>Create a new measure to calculate:</P><LI-CODE lang="markup">2008result =
VAR _mintradeday =
CALCULATE (
MIN ( 'Percentage Change % Data'[Trade Day] ),
ALL ( 'Percentage Change % Data' )
)
RETURN
IF (
SELECTEDVALUE ( 'Percentage Change % Data'[Trade Day] ) = _mintradeday,
1,
1
+ CALCULATE (
[_2008],
FILTER (
ALL ( 'Percentage Change % Data' ),
'Percentage Change % Data'[Trade Day]
<= SELECTEDVALUE ( 'Percentage Change % Data'[Trade Day] )
)
)
)</LI-CODE><P>Same logic for 2009:</P><LI-CODE lang="markup">2009result =
VAR _mintradeday =
CALCULATE (
MIN ( 'Percentage Change % Data'[Trade Day] ),
ALL ( 'Percentage Change % Data' )
)
RETURN
IF (
SELECTEDVALUE ( 'Percentage Change % Data'[Trade Day] ) = _mintradeday,
1,
1
+ CALCULATE (
[_2009],
FILTER (
ALL ( 'Percentage Change % Data' ),
'Percentage Change % Data'[Trade Day]
<= SELECTEDVALUE ( 'Percentage Change % Data'[Trade Day] )
)
)
)</LI-CODE><P>Result:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="result.png" style="width: 286px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/287430iACB0A2F3E2E85CB7/image-size/large?v=v2&px=999" role="button" title="result.png" alt="result.png" /></span></P><P> </P><P>Sample file is attached that hopes to help you, please check and try it: <A href="https://qiuyunus-my.sharepoint.com/:u:/g/personal/linli_qiuyunus_onmicrosoft_com/ERBa1i3yBsBMgAOZ46UmAvYBKyrKvsCdX5DS55-EQ89jxg?e=poiMZD" target="_self">Use previous measure result in row to calculate the result of the following row in measure.pbix</A> </P><P> </P><P>Best Regards,<BR />Yingjie Li</P><P>If this post helps then please consider Accept it as the solution to help the other members find it more quickly.</P>Wed, 03 Jun 2020 09:12:34 GMThttps://community.powerbi.com/t5/Desktop/Use-previous-measure-result-in-row-to-calculate-the-result-of/m-p/1138425#M517671v-yingjl2020-06-03T09:12:34ZRe: Use previous measure result in row to calculate the result of the following row in measure
https://community.powerbi.com/t5/Desktop/Use-previous-measure-result-in-row-to-calculate-the-result-of/m-p/1139592#M517988
<P>Hi <LI-USER uid="199176"></LI-USER> !</P><P> </P><P>Thanks so much for looking into this.</P><P> </P><P>The problem with creating a measure for each year is that the dataset keeps increasing as new contract years come in, so I would have to update the measure all the time.</P><P> </P><P>look at the raw data if I filter by trade day 2 (out of 250 for ech contract) the dataset will keep adding 250 index records for each contract year coming in. In 2021 = 250 price records will come.</P><P> </P><P>the index will always be 250 but the contract years will keep increasing</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Capturetable.PNG" style="width: 783px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/287757i02242B3CD8EC4FA6/image-size/large?v=v2&px=999" role="button" title="Capturetable.PNG" alt="Capturetable.PNG" /></span></P><P> </P><P>How do I attach a Power Bi like you did? maybe I could attach it so you could see better what I have done.</P><P> </P><P>Thanks so much</P>Wed, 03 Jun 2020 17:15:18 GMThttps://community.powerbi.com/t5/Desktop/Use-previous-measure-result-in-row-to-calculate-the-result-of/m-p/1139592#M517988reynags912020-06-03T17:15:18ZRe: Use previous measure result in row to calculate the result of the following row in measure
https://community.powerbi.com/t5/Desktop/Use-previous-measure-result-in-row-to-calculate-the-result-of/m-p/1140264#M518200
<P>Hi <LI-USER uid="233051"></LI-USER> ,</P><P>You can attach it by this link option when you reply:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="link.png" style="width: 554px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/287915i8CA2AF1DEAEB77E2/image-size/large?v=v2&px=999" role="button" title="link.png" alt="link.png" /></span></P><P> </P><P>Best Regards,<BR />Yingjie Li</P>Thu, 04 Jun 2020 01:04:05 GMThttps://community.powerbi.com/t5/Desktop/Use-previous-measure-result-in-row-to-calculate-the-result-of/m-p/1140264#M518200v-yingjl2020-06-04T01:04:05ZRe: Use previous measure result in row to calculate the result of the following row in measure
https://community.powerbi.com/t5/Desktop/Use-previous-measure-result-in-row-to-calculate-the-result-of/m-p/1162449#M525535
<P>Hi <LI-USER uid="233051"></LI-USER> ,</P><P>If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly. Thanks!</P><P><BR />Best Regards,<BR />Yingjie Li</P>Tue, 16 Jun 2020 09:02:20 GMThttps://community.powerbi.com/t5/Desktop/Use-previous-measure-result-in-row-to-calculate-the-result-of/m-p/1162449#M525535v-yingjl2020-06-16T09:02:20Z