cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
reynags91
Helper III
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

Capturef1.PNG

 

 

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
v-yingjl
Community Support
Community Support

Hi @reynags91 ,

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

table.png

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

Same logic for 2009:

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

Result:

result.png

 

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.

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

Capturetable.PNG

 

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

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

Hi @reynags91 ,

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

link.png

 

Best Regards,
Yingjie Li

Greg_Deckler
Super User IV
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!




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 Day20082010
100
2-0.016

0.000

30.0140.010
40.0080.010
50.005-0.009
6-0.0050.000
70.0000.000
8-0.0050.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 Day20082009
111
20.9841.000
30.9971.010
41.0051.019
51.0111.010
61.0051.010
71.0051.010
81.0001.014
90.9951.010

 

 

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 

 

 

@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!




Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.