Calculate daily, monthly and yearly stock price returns

[Anonymous]

Hi there

I am trying to calculate the daily, monthly and yearly stock price returns based on the latest date in the dataset.

My data set has three elements (google drive data provided below):

• Ticker - stock code (5 stocks I am tracking)
• Date - Daily close date for the past 5 years
• Adjusted closing price

https://drive.google.com/file/d/1AJVRGyYNoO5uKdCPXILc0x7QSKQ6Qirg/view?usp=sharing

Basically, I two issues:

• I tried using dateadd, however I get errors as the dates are duplicated for each ticker (i.e. each trading date will have 5 price results). Likewise, filtering the data does not seem to resolve this (through Calculate).
• Because the data is based on trading dates, it does not include all 365 days and as such I need the formula to look up the value on the nearest date 1 month and 1 year ago in order to calculate the returns

I've been bashing my head against a wall trying to create a measure for this - close to giving up and going back to excel!!

Any help would be deeply appreciated 

[amitchandak · Accepted solution]

@Anonymous . With an independent date table last know price

share price =
var _max = maxx(allselected('DATE'),'DATE'[Date])
var _min = maxx(filter(Table, Table[Date] <_max), Table[Date])
return
calculate(Max(Table[share price]), filter (Table, Table[Date] =_min))

Or with a date table

share price =
var _max = maxx(allselected('DATE'),'DATE'[Date])
var _min = maxx(filter(Table, Table[Date] <_max), Table[Date])
return
calculate(Max(Table[share price]), filter (all(Table), Table[Date] =_min))

share price =
var _max = maxx(allselected('DATE'),'DATE'[Date])
var _min = maxx(filter(Table, Table[Date] <_max), Table[Date])
return
calculate(Max(Table[share price]), filter (all(Table[date]), Table[Date] =_min))

[Anonymous]

Hi Amitchandak

Thank you for your suggestion - am I correct in saying this formula would only work in a filtered data range for a specific KPI etc? Is there an easy way to present this in a concise table for all the tickers?

[amitchandak]

@Anonymous , Can share an example table of expected output. I will try to create that.