Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Hi @Anonymous ,
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 =
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:
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
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 @Anonymous ,
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 @Anonymous ,
You can attach it by this link option when you reply:
Best Regards,
Yingjie Li
@Anonymous - 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/637614#M321
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.000 |
3 | 0.014 | 0.010 |
4 | 0.008 | 0.010 |
5 | 0.005 | -0.009 |
6 | -0.005 | 0.000 |
7 | 0.000 | 0.000 |
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.000 |
3 | 0.997 | 1.010 |
4 | 1.005 | 1.019 |
5 | 1.011 | 1.010 |
6 | 1.005 | 1.010 |
7 | 1.005 | 1.010 |
8 | 1.000 | 1.014 |
9 | 0.995 | 1.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
@Anonymous - 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
98 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |