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 inPower 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 attemptI 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