Returns for year, quarter, month for amazon stock

[hagosusng]

I have a table that contains the open, close, high, low, and adjusted close prices for Amazon from 2015 to 2021.

Excel document 

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.

[hagosusng]

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

[hagosusng]