% Variation

[Anonymous]

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!

[FreemanZ]

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:

[Anonymous]

Hi @FreemanZ !

Thanks, that worked! Just need a small adjustment. How do I get rid of those date that don't have any values?

[FreemanZ]

Hi  try like:

VAR% =

VAR _minprice =

CALCULATE(

    MIN(TableName[Value]),

    ALL(TableName),

    VALUES(TableName[Stock])

)

VAR _price = MIN(TableName[Value])

RETURN

IF(

    _price<>0,

    DIVIDE(_price- _minprice ,  _minprice)

)

@Anonymous

[Anonymous]

@FreemanZ I had to rephrase the problem. 

The solution you present is fine, but it only takes in account that the values from the Value column are always growing. So, I changed the table and what I am looking for is the oldest value for a given Stock and not the minimum. 

Cheers!

[FreemanZ · Accepted solution]

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

[Anonymous]

Since the value of a stock can go to zero, I would just change _price <>0 to NOT(ISBLANK(_price))

[FreemanZ]

this is with less codes and shall also work:

VAR%4 = 
VAR _price = MAX(TableName[Value])
VAR _earliestprice =
CALCULATE(
    MIN( TableName[Value]),
    TOPN(
        1,
        CALCULATETABLE(TableName, ALLEXCEPT( TableName, TableName[Stock])),
        TableName[MaxDate],
        ASC
    )
)
RETURN
IF(
    _price<>0,
    DIVIDE(_price- _earliestprice, _earliestprice)
)