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

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
FreemanZ
Super User
3 years ago

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

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

       

      • FreemanZ's avatar
        FreemanZ
        Super User
        3 years ago

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