Daily Return [filtered stock price % change]

[Anonymous]

Hello,

can you please help me to find a custom formula or quick measure to calculate these daily retun of stocks price?

table:

ID	OBS_DATE MM/DD/YYYY	PRICE	WANTED COLUM - RETURN daily % change
100018387	3/2/2020	64.77	-----
100018387	3/3/2020	63.78	-1.53%
100018387	3/4/2020	68.05	6.69%
100030418	3/2/2020	38.27	-----
100030418	3/3/2020	37.23	-2.72%
100030418	3/4/2020	38.75	4.08%

 Thank you!!

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can create one measure as below:

Daily % change = var curdate=MAX('Daily Return'[OBS_DATE])
var predate=CALCULATE(MAX('Daily Return'[OBS_DATE]),'Daily Return'[OBS_DATE]<curdate)
var priceinPredate=CALCULATE(MAX('Daily Return'[PRICE]),'Daily Return'[OBS_DATE]=predate)
return IF(NOT(ISBLANK(priceinPredate)),DIVIDE(MAX('Daily Return'[PRICE])-priceinPredate,MAX('Daily Return'[PRICE])))

Best Regards

Rena

[Anonymous]

Hi @v-yiruan-msft Rena, 

Thank you for the support! This is exactly what I was looking for.

If possible, could you please tell how would I add this daily % change as a custom colum instead of a measure? Will this have any impact on performaces?

Much appreciate, regards,

Luca.

[v-yiruan-msft]

Hi @Anonymous ，

The biggest difference between calculated column and measure is that calculated column is stored in memory, consuming a certain amount of memory. It will affect the performance of query calculations when the amount of data is large. You can find the details in this post.

You can also create one calculated column as below base on your scenario:

Prevprice = var a ='Daily Return'[OBS_DATE]
var b= CALCULATE(MAX('Daily Return'[OBS_DATE]),FILTER('Daily Return','Daily Return'[ID]=EARLIER('Daily Return'[ID])&&'Daily Return'[OBS_DATE]<a)) 
var c= CALCULATE(max('Daily Return'[PRICE]),FILTER('Daily Return','Daily Return'[ID]=EARLIER('Daily Return'[ID])&&'Daily Return'[OBS_DATE]=b))
return IF(NOT(ISBLANK(c)),DIVIDE('Daily Return'[PRICE]-c,'Daily Return'[PRICE]))

Best Regards

Rena

[camargos88]

@Anonymous ,

You can try this measure:

Measure =

VAR _lastday = CALCULATE(PREVIOUSDAY('Table'[OBS_DATE]))

VAR _ID = CALCULATE(MIN('Table'[ID]); FILTER(ALL('Table'); 'Table'[OBS_DATE] = _lastday))

VAR _lastPrice = CALCULATE(SUM('Table'[PRICE]); FILTER(ALL('Table'); 'Table'[OBS_DATE] = _lastday && 'Table'[ID] = _ID))

RETURN DIVIDE(SUM('Table'[PRICE]) - _lastPrice; _lastPrice; 0)

However you have more than 1 date, maybe you have the stock name ??? If yes, you can use this:

Measure =

VAR _lastday = CALCULATE(PREVIOUSDAY('Table'[OBS_DATE]))

VAR _lastPrice = CALCULATE(SUM('Table'[PRICE]); FILTER(ALLEXCEPT('Table'; ___STOCKNAME___); 'Table'[OBS_DATE] = _lastday))

RETURN DIVIDE(SUM('Table'[PRICE]) - _lastPrice; _lastPrice; 0)

With this you can get the last date by stock name.

Ricardo