cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Calculate daily, monthly and yearly stock price returns

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 

1 ACCEPTED SOLUTION
Super User IV
Super User IV

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

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

3 REPLIES 3
Super User IV
Super User IV

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

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Anonymous
Not applicable

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?

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors