Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have a table that contains the open, close, high, low, and adjusted close prices for Amazon from 2015 to 2021.
My goal is to produce returns numbers (either individually or in a matrix as below) for year, each quarter of a selected year, each month of a selected quarter and year, and each week of a selected year in Power BI. I am only using Excel as a transition phase . Please note that I didn't included week numbers in my attempts below (this is because weekly are 'a nice to have', I can live without them for now)
Below is a snapshot of what I want to achieve and the things I am getting
Excel attempt I added 3 additional columns
(1) DailyReturn (using F3/F2-1 )
(2) Prevalue (using H2 )
(3) Cummulative Return (using ((1+J2)*(1+H3))-1 )
Below is the pivot selection that I made to produce the pivot table above.
Power BI attempt
Name of table is 'Amazon2'.
'Adjusted close' is the column with the values
I created three (3) additional columns -
(1) Index column - starting with 1
(2) PreValue - this is to capture the previous day adjusted close value
CALCULATE(MAX(Amazon2[Adj Close]), ALL(Amazon2), Amazon2[Index] = EARLIER(Amazon2[Index])-1 )
(3) Return - this gives daily report
IF(Amazon2[PreValue] = BLANK(), BLANK(), (Amazon2[Adj Close]/Amazon2[PreValue])-1)
Below is the snapshot of my selections in Power BI
and my Power BI output
I am happy to get any suggestions. Thank you.
Date | Open | High | Low | Close | Adj Close | Volume |
01/01/2015 | 15.58 | 15.65 | 15.5 | 15.52 | 15.52 | 40960000 |
02/01/2015 | 15.629 | 15.7375 | 15.348 | 15.426 | 15.426 | 55664000 |
03/01/2015 | 15.426 | 15.7375 | 15.348 | 15.426 | 15.426 | 0 |
04/01/2015 | 15.426 | 15.7375 | 15.348 | 15.426 | 15.426 | 0 |
05/01/2015 | 15.3505 | 15.419 | 15.0425 | 15.1095 | 15.1095 | 55484000 |
06/01/2015 | 15.112 | 15.15 | 14.619 | 14.7645 | 14.7645 | 70380000 |
07/01/2015 | 14.875 | 15.064 | 14.7665 | 14.921 | 14.921 | 52806000 |
08/01/2015 | 15.016 | 15.157 | 14.8055 | 15.023 | 15.023 | 61768000 |
09/01/2015 | 15.074 | 15.1435 | 14.834 | 14.8465 | 14.8465 | 51848000 |
10/01/2015 | 14.8465 | 15.1435 | 14.834 | 14.8465 | 14.8465 | 0 |
11/01/2015 | 14.8465 | 15.1435 | 14.834 | 14.8465 | 14.8465 | 0 |
12/01/2015 | 14.878 | 14.9255 | 14.464 | 14.5705 | 14.5705 | 68428000 |
13/01/2015 | 14.874 | 15.075 | 14.6615 | 14.737 | 14.737 | 82728000 |
14/01/2015 | 14.5965 | 14.7955 | 14.325 | 14.6635 | 14.6635 | 1.11E+08 |
15/01/2015 | 14.7 | 14.8 | 14.341 | 14.3475 | 14.3475 | 88384000 |
16/01/2015 | 14.314 | 14.5395 | 14.2625 | 14.537 | 14.537 | 69564000 |
17/01/2015 | 14.537 | 14.5395 | 14.2625 | 14.537 | 14.537 | 0 |
18/01/2015 | 14.537 | 14.5395 | 14.2625 | 14.537 | 14.537 | 0 |
19/01/2015 | 14.537 | 14.5395 | 14.2625 | 14.537 | 14.537 | 0 |
20/01/2015 | 14.6295 | 14.668 | 14.3195 | 14.472 | 14.472 | 61502000 |
21/01/2015 | 14.482 | 15.3 | 14.363 | 14.8625 | 14.8625 | 2.01E+08 |
22/01/2015 | 15 | 15.6125 | 15 | 15.516 | 15.516 | 1.07E+08 |
23/01/2015 | 15.404 | 15.8465 | 15.386 | 15.6195 | 15.6195 | 89928000 |
24/01/2015 | 15.6195 | 15.8465 | 15.386 | 15.6195 | 15.6195 | 0 |
25/01/2015 | 15.6195 | 15.8465 | 15.386 | 15.6195 | 15.6195 | 0 |
26/01/2015 | 15.591 | 15.65 | 15.376 | 15.483 | 15.483 | 63394000 |
27/01/2015 | 15.315 | 15.512 | 15.1315 | 15.3375 | 15.3375 | 58406000 |
28/01/2015 | 15.4905 | 15.5755 | 15.19 | 15.1955 | 15.1955 | 61276000 |
29/01/2015 | 15.2365 | 15.64 | 14.9665 | 15.589 | 15.589 | 1.73E+08 |
30/01/2015 | 17.316 | 17.975 | 17.037001 | 17.7265 | 17.7265 | 4.77E+08 |
31/01/2015 | 17.7265 | 17.975 | 17.037001 | 17.7265 | 17.7265 | 0 |
01/02/2015 | 17.7265 | 17.975 | 17.037001 | 17.7265 | 17.7265 | 0 |
02/02/2015 | 17.502501 | 18.25 | 17.5005 | 18.223499 | 18.223499 | 2.05E+08 |
03/02/2015 | 18.0145 | 18.391001 | 18.0105 | 18.1775 | 18.1775 | 1.24E+08 |
04/02/2015 | 17.919001 | 18.375 | 17.911501 | 18.237499 | 18.237499 | 83398000 |
05/02/2015 | 18.299999 | 18.940001 | 18.295 | 18.6945 | 18.6945 | 1.45E+08 |
06/02/2015 | 18.7435 | 18.7995 | 18.550501 | 18.714001 | 18.714001 | 77848000 |
07/02/2015 | 18.714001 | 18.7995 | 18.550501 | 18.714001 | 18.714001 | 0 |
08/02/2015 | 18.714001 | 18.7995 | 18.550501 | 18.714001 | 18.714001 | 0 |
09/02/2015 | 18.549999 | 18.720501 | 18.360001 | 18.528 | 18.528 | 54492000 |
10/02/2015 | 18.5595 | 18.715 | 18.425501 | 18.65 | 18.65 | 45744000 |
11/02/2015 | 18.561501 | 18.855 | 18.555 | 18.757 | 18.757 | 55730000 |
12/02/2015 | 18.754999 | 18.925501 | 18.657499 | 18.8585 | 18.8585 | 55822000 |
13/02/2015 | 18.9205 | 19.15 | 18.8505 | 19.091499 | 19.091499 | 69502000 |
14/02/2015 | 19.091499 | 19.15 | 18.8505 | 19.091499 | 19.091499 | 0 |
15/02/2015 | 19.091499 | 19.15 | 18.8505 | 19.091499 | 19.091499 | 0 |
16/02/2015 | 19.091499 | 19.15 | 18.8505 | 19.091499 | 19.091499 | 0 |
17/02/2015 | 18.886 | 18.9995 | 18.633499 | 18.7715 | 18.7715 | 73464000 |
18/02/2015 | 18.688999 | 18.837 | 18.6425 | 18.668501 | 18.668501 | 53012000 |
19/02/2015 | 18.6745 | 19.094 | 18.671499 | 18.950001 | 18.950001 | 59076000 |
20/02/2015 | 18.931 | 19.184999 | 18.792 | 19.183001 | 19.183001 | 65160000 |
21/02/2015 | 19.183001 | 19.184999 | 18.792 | 19.183001 | 19.183001 | 0 |
22/02/2015 | 19.183001 | 19.184999 | 18.792 | 19.183001 | 19.183001 | 0 |
23/02/2015 | 19.172001 | 19.226999 | 18.9 | 19.007 | 19.007 | 43540000 |
24/02/2015 | 18.932501 | 19.024 | 18.808001 | 18.929501 | 18.929501 | 38416000 |
25/02/2015 | 18.863501 | 19.3925 | 18.827 | 19.268499 | 19.268499 | 63490000 |
26/02/2015 | 19.203501 | 19.4685 | 19.164 | 19.24 | 19.24 | 53742000 |
27/02/2015 | 19.200001 | 19.2995 | 18.9895 | 19.007999 | 19.007999 | 50646000 |
28/02/2015 | 19.007999 | 19.2995 | 18.9895 | 19.007999 | 19.007999 | 0 |
01/03/2015 | 19.007999 | 19.2995 | 18.9895 | 19.007999 | 19.007999 | 0 |
02/03/2015 | 19.0425 | 19.295 | 18.974001 | 19.283001 | 19.283001 | 42784000 |
03/03/2015 | 19.1975 | 19.305 | 19.08 | 19.230499 | 19.230499 | 38930000 |
04/03/2015 | 19.2855 | 19.395 | 19.109501 | 19.136 | 19.136 | 45182000 |
05/03/2015 | 19.2805 | 19.421 | 19.1945 | 19.3915 | 19.3915 | 53870000 |
06/03/2015 | 19.275999 | 19.35 | 18.944 | 19.004499 | 19.004499 | 52540000 |
07/03/2015 | 19.004499 | 19.35 | 18.944 | 19.004499 | 19.004499 | 0 |
08/03/2015 | 19.004499 | 19.35 | 18.944 | 19.004499 | 19.004499 | 0 |
09/03/2015 | 18.92 | 18.966499 | 18.764 | 18.927999 | 18.927999 | 46228000 |
10/03/2015 | 18.872499 | 18.8885 | 18.459 | 18.4755 | 18.4755 | 62420000 |
11/03/2015 | 18.5305 | 18.6675 | 18.313 | 18.318501 | 18.318501 | 49902000 |
12/03/2015 | 18.441 | 18.775 | 18.375999 | 18.712 | 18.712 | 56200000 |
13/03/2015 | 18.576 | 18.699499 | 18.334 | 18.528999 | 18.528999 | 52234000 |
14/03/2015 | 18.528999 | 18.699499 | 18.334 | 18.528999 | 18.528999 | 0 |
15/03/2015 | 18.528999 | 18.699499 | 18.334 | 18.528999 | 18.528999 | 0 |
16/03/2015 | 18.518999 | 18.697001 | 18.336 | 18.6675 | 18.6675 | 47082000 |
17/03/2015 | 18.5555 | 18.725 | 18.482 | 18.596001 | 18.596001 | 40740000 |
18/03/2015 | 18.498501 | 18.7995 | 18.334999 | 18.757 | 18.757 | 53082000 |
19/03/2015 | 18.693501 | 18.855499 | 18.609501 | 18.662001 | 18.662001 | 38314000 |
20/03/2015 | 18.814501 | 18.9685 | 18.796 | 18.9245 | 18.9245 | 75272000 |
21/03/2015 | 18.9245 | 18.9685 | 18.796 | 18.9245 | 18.9245 | 0 |
22/03/2015 | 18.9245 | 18.9685 | 18.796 | 18.9245 | 18.9245 | 0 |
23/03/2015 | 18.9035 | 19.088499 | 18.747 | 18.755501 | 18.755501 | 44786000 |
24/03/2015 | 18.699499 | 18.761999 | 18.613501 | 18.7045 | 18.7045 | 44564000 |
25/03/2015 | 18.758499 | 19.025 | 18.5145 | 18.548 | 18.548 | 68590000 |
26/03/2015 | 18.4795 | 18.57 | 18.282499 | 18.3675 | 18.3675 | 58600000 |
27/03/2015 | 18.355499 | 18.658501 | 18.328501 | 18.528 | 18.528 | 52196000 |
28/03/2015 | 18.528 | 18.658501 | 18.328501 | 18.528 | 18.528 | 0 |
29/03/2015 | 18.528 | 18.658501 | 18.328501 | 18.528 | 18.528 | 0 |
30/03/2015 | 18.5935 | 18.806 | 18.577499 | 18.7295 | 18.7295 | 36418000 |
31/03/2015 | 18.662001 | 18.885 | 18.5755 | 18.605 | 18.605 | 50122000 |
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
84 | |
83 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |