Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
hagosusng
Frequent Visitor

Returns for year, quarter, month for amazon stock

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 

 

Snapshot.png

 

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.

 

Pivot Snapsot.png

 

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 

 

Snapshot - PBI.png

Snapshot - PBI output.png

 

and my Power BI output

 

 

I am happy to get any suggestions. Thank you.

2 REPLIES 2
hagosusng
Frequent Visitor

DateOpenHighLowCloseAdj CloseVolume
01/01/201515.5815.6515.515.5215.5240960000
02/01/201515.62915.737515.34815.42615.42655664000
03/01/201515.42615.737515.34815.42615.4260
04/01/201515.42615.737515.34815.42615.4260
05/01/201515.350515.41915.042515.109515.109555484000
06/01/201515.11215.1514.61914.764514.764570380000
07/01/201514.87515.06414.766514.92114.92152806000
08/01/201515.01615.15714.805515.02315.02361768000
09/01/201515.07415.143514.83414.846514.846551848000
10/01/201514.846515.143514.83414.846514.84650
11/01/201514.846515.143514.83414.846514.84650
12/01/201514.87814.925514.46414.570514.570568428000
13/01/201514.87415.07514.661514.73714.73782728000
14/01/201514.596514.795514.32514.663514.66351.11E+08
15/01/201514.714.814.34114.347514.347588384000
16/01/201514.31414.539514.262514.53714.53769564000
17/01/201514.53714.539514.262514.53714.5370
18/01/201514.53714.539514.262514.53714.5370
19/01/201514.53714.539514.262514.53714.5370
20/01/201514.629514.66814.319514.47214.47261502000
21/01/201514.48215.314.36314.862514.86252.01E+08
22/01/20151515.61251515.51615.5161.07E+08
23/01/201515.40415.846515.38615.619515.619589928000
24/01/201515.619515.846515.38615.619515.61950
25/01/201515.619515.846515.38615.619515.61950
26/01/201515.59115.6515.37615.48315.48363394000
27/01/201515.31515.51215.131515.337515.337558406000
28/01/201515.490515.575515.1915.195515.195561276000
29/01/201515.236515.6414.966515.58915.5891.73E+08
30/01/201517.31617.97517.03700117.726517.72654.77E+08
31/01/201517.726517.97517.03700117.726517.72650
01/02/201517.726517.97517.03700117.726517.72650
02/02/201517.50250118.2517.500518.22349918.2234992.05E+08
03/02/201518.014518.39100118.010518.177518.17751.24E+08
04/02/201517.91900118.37517.91150118.23749918.23749983398000
05/02/201518.29999918.94000118.29518.694518.69451.45E+08
06/02/201518.743518.799518.55050118.71400118.71400177848000
07/02/201518.71400118.799518.55050118.71400118.7140010
08/02/201518.71400118.799518.55050118.71400118.7140010
09/02/201518.54999918.72050118.36000118.52818.52854492000
10/02/201518.559518.71518.42550118.6518.6545744000
11/02/201518.56150118.85518.55518.75718.75755730000
12/02/201518.75499918.92550118.65749918.858518.858555822000
13/02/201518.920519.1518.850519.09149919.09149969502000
14/02/201519.09149919.1518.850519.09149919.0914990
15/02/201519.09149919.1518.850519.09149919.0914990
16/02/201519.09149919.1518.850519.09149919.0914990
17/02/201518.88618.999518.63349918.771518.771573464000
18/02/201518.68899918.83718.642518.66850118.66850153012000
19/02/201518.674519.09418.67149918.95000118.95000159076000
20/02/201518.93119.18499918.79219.18300119.18300165160000
21/02/201519.18300119.18499918.79219.18300119.1830010
22/02/201519.18300119.18499918.79219.18300119.1830010
23/02/201519.17200119.22699918.919.00719.00743540000
24/02/201518.93250119.02418.80800118.92950118.92950138416000
25/02/201518.86350119.392518.82719.26849919.26849963490000
26/02/201519.20350119.468519.16419.2419.2453742000
27/02/201519.20000119.299518.989519.00799919.00799950646000
28/02/201519.00799919.299518.989519.00799919.0079990
01/03/201519.00799919.299518.989519.00799919.0079990
02/03/201519.042519.29518.97400119.28300119.28300142784000
03/03/201519.197519.30519.0819.23049919.23049938930000
04/03/201519.285519.39519.10950119.13619.13645182000
05/03/201519.280519.42119.194519.391519.391553870000
06/03/201519.27599919.3518.94419.00449919.00449952540000
07/03/201519.00449919.3518.94419.00449919.0044990
08/03/201519.00449919.3518.94419.00449919.0044990
09/03/201518.9218.96649918.76418.92799918.92799946228000
10/03/201518.87249918.888518.45918.475518.475562420000
11/03/201518.530518.667518.31318.31850118.31850149902000
12/03/201518.44118.77518.37599918.71218.71256200000
13/03/201518.57618.69949918.33418.52899918.52899952234000
14/03/201518.52899918.69949918.33418.52899918.5289990
15/03/201518.52899918.69949918.33418.52899918.5289990
16/03/201518.51899918.69700118.33618.667518.667547082000
17/03/201518.555518.72518.48218.59600118.59600140740000
18/03/201518.49850118.799518.33499918.75718.75753082000
19/03/201518.69350118.85549918.60950118.66200118.66200138314000
20/03/201518.81450118.968518.79618.924518.924575272000
21/03/201518.924518.968518.79618.924518.92450
22/03/201518.924518.968518.79618.924518.92450
23/03/201518.903519.08849918.74718.75550118.75550144786000
24/03/201518.69949918.76199918.61350118.704518.704544564000
25/03/201518.75849919.02518.514518.54818.54868590000
26/03/201518.479518.5718.28249918.367518.367558600000
27/03/201518.35549918.65850118.32850118.52818.52852196000
28/03/201518.52818.65850118.32850118.52818.5280
29/03/201518.52818.65850118.32850118.52818.5280
30/03/201518.593518.80618.57749918.729518.729536418000
31/03/201518.66200118.88518.575518.60518.60550122000
hagosusng
Frequent Visitor

hagosusng_0-1684946267008.png

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.