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 d...
  • 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

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

Hi CharlesRondo,

 

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

Stock_prices_return_graph

 

 

Best Regards,

Dale

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

    • CharlesRondo's avatar
      CharlesRondo
      Frequent Visitor
      8 years ago

      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!