2

I have stock prices in a table:

Date Company Price
2022-07-06 AAPL 142.92
2022-07-06 AMZN 114.33
2022-07-06 MSFT 266.21
2022-07-07 AAPL 146.35
2022-07-07 AMZN 116.33
2022-07-07 MSFT 268.4
2022-07-08 AAPL 147.04
2022-07-08 AMZN 115.54
2022-07-08 MSFT 267.66
2022-07-11 AAPL 144.87
2022-07-11 AMZN 111.75
2022-07-11 MSFT 264.51
2022-07-12 AAPL 145.86
2022-07-12 AMZN 109.22
2022-07-12 MSFT 253.67

I want to calculate day-to-day price changes and price returns by company. The result should look like this:

Date AAPL Price AAPL Change AAPL Return AMZN Price AMZN Change AMZN Return MSFT Price MSFT Change MSFT Return
2022-07-06 142.92 114.33 266.21
2022-07-07 146.35 3.43 2.4% 116.33 2.00 1.7% 268.4 2.19 0.8%
2022-07-08 147.04 0.69 0.5% 115.54 -0.79 -0.7% 267.66 -0.74 -0.3%
2022-07-11 144.87 -2.17 -1.5% 111.75 -3.79 -3.3% 264.51 -3.15 -1.2%
2022-07-12 145.86 0.99 0.7% 109.22 -2.53 -2.3% 253.67 -10.84 -4.1%
Peter
  • 10,959
  • 2
  • 30
  • 47
  • Do you definitely want to do it in DAX? Presumably as a calculated table. Also, do you definitely want your table to be wide as opposed to narrow with a single column for company that you can then filter on? – Davide Bacci Jul 20 '22 at 16:23
  • I understand that "wide vs. long format" is just a matter of choosing the matrix visual over the table visual - as long as I have all the columns. – Peter Jul 20 '22 at 16:33
  • 1
    Do you need a table? If you are going to use a visual then it will be just 2 simple measures only. – Mik Jul 20 '22 at 17:06
  • Tell me more ... – Peter Jul 20 '22 at 17:16

1 Answers1

3

Here you go.

enter image description here

Use a matrix and add these 3 measures.

Price Measure = MAX('Table'[Price]) 

Change = 
VAR cursor = MAX('Table'[Date])
VAR previousDate = CALCULATE(MAX('Table'[Date]), 'Table'[Date] < cursor)
VAR previousPrice = CALCULATE([Price Measure],'Table'[Date] = previousDate)

RETURN 
IF(NOT(ISBLANK(previousPrice)),  [Price Measure] - previousPrice)

Return = [Change]/[Price Measure]

enter image description here

S&P Link referred to in the comment

Davide Bacci
  • 16,647
  • 3
  • 10
  • 36
  • 1
    Amazingly clean solution - thanks! – Peter Jul 20 '22 at 17:36
  • 1
    Peter - if you're comfortable with Deneb (custom visual in PowerBI), then I recently created the following which is S&P performance over the last year. You can easily have this in PowerBI. I have to add the link to the answer at the end as the url is too long for a comment. Click to animate the visual to see it cycle through each day. – Davide Bacci Jul 20 '22 at 18:38