March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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 |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
129 | |
90 | |
75 | |
58 | |
53 |
User | Count |
---|---|
200 | |
104 | |
101 | |
67 | |
55 |