Stock prices return graph

[CharlesRondo]

Hello, begginer user here!

I want to create a line chart with the every day returns of stock prices, the problem for me is that I want it to by dynamic, being able to chose the initial and the end date of analysis. 

The chart in excel and data prices look like this: 

	F	GM	FCHA.MI
Timestamp	Trade Close	Trade Close	Trade Close
04/07/2018	10.99	38.97	16.21
03/07/2018	10.99	38.97	16.33
02/07/2018	11.10	39.50	16.34
01/07/2018	11.07	39.40	16.33
30/06/2018	11.07	39.40	16.33
29/06/2018	11.07	39.40	16.33
28/06/2018	11.28	40.52	16.04
27/06/2018	11.42	40.37	16.62
26/06/2018	11.52	41.01	16.44
25/06/2018	11.50	40.61	15.99
24/06/2018	11.65	41.25	16.50
23/06/2018	11.65	41.25	16.50

Thanks!

[Thejeswar]

Hi,

You can use a Date Slicer that will help you dynamically get the Dates of interest

Like the one below?

[v-jiascu-msft]

Hi @CharlesRondo,

You can use a Slicer with date field. Please refer to the snapshot below.

Best Regards,

Dale

[CharlesRondo]

Thanks for the reply... but, well that's just part of the process, the real issue is that I need to get the returns depending on what I put on the slicer, in other words, if  I'd like to graph, lets say one day of returns, what we would do in excel is=

% Return of X in date 1= (Trade Close Price at date 1 / Trade Close Price previous day) -1

Then you graph:

Return at date X0= 0 % (First number to graph is alway cero) 

Return at date X+1= (Trade Close Price at X+1  / Trade Close Price at date X0 ) - 1

Return at date 2= (Trade Close Price at date X+2  / Trade Close Price at date X0 ) - 1

Return at date 3= (Trade Close Price at date X+3  / Trade Close Price at date X0 ) - 1

And so on...

Note that the base price is always fixed, and I need to be able to chose that with the slicer because it can start at any date.  

The following table shows daily returns at a specific date or price base... and then I need graph this  

[v-jiascu-msft · Accepted solution]

Hi @CharlesRondo,

In your scenario, you may need three or more date tables due to the start dates are different and independent. Please check out the demo in the attachment. Three measures are like below. (Please note: only F and GM are the same with your requirements here. You can adjust the FCA yourself.)

F =
VAR minDate =
    CALCULATE ( MIN ( Table1[Timestamp] ), ALLSELECTED ( Table1[Timestamp] ) )
RETURN
    IF (
        MIN ( Table1[Timestamp] ) = minDate,
        0,
        DIVIDE (
            SUM ( Table1[Trade Close] ),
            CALCULATE ( SUM ( Table1[Trade Close] ), 'Table1'[Timestamp] = minDate )
        )
            - 1
    )

GM =
VAR minDate =
    CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
RETURN
    IF (
        MIN ( Table1[Timestamp] ) <= minDate,
        0,
        DIVIDE (
            SUM ( Table1[Trade Close .1] ),
            CALCULATE ( SUM ( Table1[Trade Close .1] ), 'Table1'[Timestamp] = minDate )
        )
            - 1
    )

FCA =
VAR minDate =
    CALCULATE ( MIN ( Table1[Timestamp] ), ALLSELECTED ( Table1[Timestamp] ) )
RETURN
    IF (
        MIN ( Table1[Timestamp] ) = minDate,
        0,
        DIVIDE (
            SUM ( Table1[Trade Close .2] ),
            CALCULATE ( SUM ( Table1[Trade Close .2] ), 'Table1'[Timestamp] = minDate )
        )
            - 1
    )

Best Regards,

Dale

[CharlesRondo]

OMG! That´s it!, works perfectly!...I need to study all the logic with the language behind since I'm more used to excel functions. Thanks!