Forum Discussion

CharlesRondo's avatar
CharlesRondo
Frequent Visitor
8 years ago
Solved

Stock prices return graph

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!

  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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
    )

    Stock_prices_return_graph2

    Best Regards,

    Dale

5 Replies

    • CharlesRondo's avatar
      CharlesRondo
      Frequent Visitor
      8 years ago

      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  :smileysad:

       

       

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        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
    )

        Stock_prices_return_graph2

        Best Regards,

        Dale

  • Thejeswar's avatar
    Thejeswar
    Super User
    8 years ago

    Hi,

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

     

    Like the one below?