topic Re: % Variation in DAX Commands and Tips

% Variation

Anonymous — Thu, 22 Dec 2022 12:40:06 GMT

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!

Re: % Variation

FreemanZ — Wed, 21 Dec 2022 13:06:59 GMT

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:

Re: % Variation

Anonymous — Wed, 21 Dec 2022 13:16:59 GMT

Hi FreemanZ !

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

Re: % Variation

FreemanZ — Wed, 21 Dec 2022 13:20:07 GMT

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

Re: % Variation

Anonymous — Thu, 22 Dec 2022 12:44:06 GMT

@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!

Re: % Variation

FreemanZ — Thu, 22 Dec 2022 13:19:55 GMT

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

Re: % Variation

FreemanZ — Thu, 22 Dec 2022 13:52:33 GMT

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

Re: % Variation

Anonymous — Thu, 22 Dec 2022 16:13:01 GMT

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