cancel
Showing results for
Did you mean:
Helper III

## Use previous measure result in row to calculate the result of the following row in measure

Hi

I found a major road block in my model. Im trying to normalize my price data.

I am using the % change based on my index which i already create a measure for.

Starting at 1

1 +(%change*1)= 0.984

Then I need to use the previous result for all subsequent records

0.9484 + (%change * 0984) and so forth for all my records

Here is the excel data sample and formula.

I need to be able to use the result from 1 in the same measure to calculate the next version and so on

=AY2+(AL3*AY2)

=AY3+(AL3*AY3)

=AY4+(AL3*AY4)

and so forth

I have tried the following measure but it doesnt work

Trade day is my index column

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))

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?

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.

Thanks a lot!

7 REPLIES 7
Community Support

Hi @reynags91 ,

Based on your description, I have created table like this:

Simple measure as 2008 and 2009:

``_2008 = SUM('Percentage Change % Data'[2008])``
``_2009 = SUM('Percentage Change % Data'[2009])``

Create a new measure to calculate:

``````2008result =
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] )
)
)
)``````

Same logic for 2009:

``````2009result =
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] )
)
)
)``````

Result:

Sample file is attached that hopes to help you, please check and try it: Use previous measure result in row to calculate the result of the following row in measure.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Helper III

Hi @v-yingjl !

Thanks so much for looking into this.

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.

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.

the index will always be 250 but the contract years will keep increasing

How do I attach a Power Bi like you did? maybe I could attach it so you could see better what I have done.

Thanks so much

Community Support

Hi @reynags91 ,

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!

Best Regards,
Yingjie Li

Community Support

Hi @reynags91 ,

You can attach it by this link option when you reply:

Best Regards,
Yingjie Li

Super User IV

@reynags91 - 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. https://community.powerbi.com/t5/Quick-Measures-Gallery/Previous-Value-aka-quot-Recursion-quot/m-p/6...

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helper III

Thanks so much for answering!

Percent change data % in my model is a measure. that shows % day over day (trade day)

Trade Day (index in my model) goes up to 250 for each contract year

Contract Year: from 2008 to 2019

This is the data

Percentage Change % data

 Trade Day 2008 2010 1 0 0 2 -0.016 0 3 0.014 0.01 4 0.008 0.01 5 0.005 -0.009 6 -0.005 0 7 0 0 8 -0.005 0.005 9 -0.005 -0.005 10 -0.005 -0.005

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.

Price Normalized: This is the numbers Im trying to achieve per trade day.

 Trade Day 2008 2009 1 1 1 2 0.984 1 3 0.997 1.01 4 1.005 1.019 5 1.011 1.01 6 1.005 1.01 7 1.005 1.01 8 1 1.014 9 0.995 1.01

In excel is done by Previous row +(percentage change row * previous row) so 1 + (- 0.016 * 1) = 0.984

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?

Thank so much for your help. I keep going in circles @Greg_Deckler

Super User IV

@reynags91 - 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.

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group