Forum Discussion
% Variation
Hey all,
I want to calculate the % variation of a stock, but I am having trouble to lock it's earliest and lastest value to perform my calculation.
| Stock | MaxDate | year-month | Value |
| A | 7/31/2022 | Jul-22 | 14 |
| A | 8/31/2022 | Aug-22 | 15 |
| A | 9/30/2022 | Sep-22 | 15 |
| A | 10/31/2022 | Oct-22 | 12 |
| A | 11/30/2022 | Nov-22 | 16 |
| B | 7/31/2022 | Jul-22 | 74 |
| B | 8/31/2022 | Aug-22 | 65 |
| B | 9/30/2022 | Sep-22 | 64 |
| B | 10/31/2022 | Oct-22 | 67 |
| B | 11/30/2022 | Nov-22 | 80 |
| C | 7/31/2022 | Jul-22 | 110 |
| C | 8/31/2022 | Aug-22 | 105 |
| C | 9/30/2022 | Sep-22 | 90 |
| C | 10/31/2022 | Oct-22 | 110 |
| C | 11/30/2022 | Nov-22 | 85 |
Besides this table, there is also a Calendar table that is connect by a Date column.
For Stock A I always want to Initial Value to be = 14. Then, as the months go by I want to:
- August % = (15-14)/14
- September = (15-14)/14
- October = (12-14)/14
- November = (16-14)/14
How can I do this? Thanks!
hi Anonymous
try like:
VAR% = VAR _price = MAX(TableName[Value]) VAR _stock = MAX(TableName[Stock]) VAR _earliestdate = //earliest date for the current stock MINX( FILTER( ALL(TableName), TableName[Stock] = _stock ), TableName[MaxDate] ) VAR _earliestprice = //value on the earliest date for the current stock MINX( FILTER( ALL(TableName), TableName[Stock] = _stock &&TableName[MaxDate]=_earliestdate ), TableName[Value] ) RETURN IF( _price<>0, DIVIDE(_price- _earliestprice, _earliestprice) )
7 Replies
hi Anonymous
try to plot a table with this:
VAR% =
VAR _minprice =
CALCULATE(
MIN(TableName[Value]),
ALL(TableName),
VALUES(TableName[Stock])
)
VAR _price = MIN(TableName[Value])
RETURN
DIVIDE(_price- _minprice , _minprice)i tried and it worked like this:Hi FreemanZ !
Thanks, that worked! Just need a small adjustment. How do I get rid of those date that don't have any values?Hi try like:
VAR% =VAR _minprice =CALCULATE(MIN(TableName[Value]),ALL(TableName),VALUES(TableName[Stock]))VAR _price = MIN(TableName[Value])RETURNIF(_price<>0,DIVIDE(_price- _minprice , _minprice))Anonymous