Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

% 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 ...
dates
Percentage Difference
Variation
  • FreemanZ's avatar
    FreemanZ
    3 years ago

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

     

FreemanZ's avatar
FreemanZ
Super User
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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