Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Normalize time series data with multiple per category with date slicer

Hi all,   I'm trying to create a line chart visual that can compare different stocks' performances over time compared to a starting period defined by a slicer.   All of the data should start at 0...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    v-piga-msft , i think i cracked it, it was just one extra step :). Thank you so much for your help! I've just introduced a new variable and used the SELECTEDVALUE function . After that i've just used the variable as a filter in the firstrecord variable (as that unfilters with the ALLSELECTED function).

     

    Thanks again for your help!

     

    Change% = 
VAR a =
    CALCULATE (
        MAX ( 'Ticker_table'[Adjusted close] ),
        FILTER (
            'Ticker_table',
            'Ticker_table'[Date] = MIN ( 'Ticker_table'[Date] )
        )
    )
VAR mindate =
    CALCULATE (
        MIN ( 'Ticker_table'[Date] ),
        ALLSELECTED ( 'Ticker_table'[Date] )
    )
VAR ticker =
    CALCULATE(
        SELECTEDVALUE(Ticker_table[Ticker])
     )

VAR firstrecord =
    CALCULATE (
        MAX ( 'Ticker_table'[Adjusted close] ),
        FILTER (
            ALLSELECTED ( 'Ticker_table' ),
            'Ticker_table'[Date] = mindate),
        Ticker_table[Ticker] = ticker
    )
RETURN
    DIVIDE ( a - firstrecord, firstrecord )
v-piga-msft's avatar
v-piga-msft
Resident Rockstar
6 years ago

Hi Anonymous ,

If I understand your requirement that you want to show the stock price per ticker on the line chart.

More details will be much helpful.

If it is convenient, could you share some data sample  which could reproduce your scenario so that we could have a test on it?

Best Regards,

Cherry

Anonymous's avatar
Anonymous
Not applicable
6 years ago

v-piga-msft thank you for your answer. Sure thing, here it goes:

 

I'd like to compare prices over time to the first data point after applying a slicer and displaying it on a line chart.

 

Date               Price          Change

9/24/2019      110            -10%

9/23/2019      95              -5%

9/22/2019      100

(9/22/2019 being the first datapoint in this example)

 

You can find the sample data following this link: https://drive.google.com/open?id=1psAt_OuPMM7oefn41ZnkX_rylVtwPGTB

- PowerBI structure sheet: how the data is organized in my Power BI dataset

- 2-mo/1-mo slider applied: shows two chart that i would like to dynamically display in Power BI using a date slicer (FYI: the data is "pivoted" for these in Excel to create the charts, so don't mind that as a reference point for my data structure in Power BI). I've also attached the charts here:

 

I hope this helps, please let me know if you need additional information.

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    6 years ago

    Hi Anonymous ,

    You could create the measure below to get the %change and it will change based on the date and Ticker slicer.

    Change% =
VAR a =
    CALCULATE (
        MAX ( 'PowerBI structure'[Adjusted close] ),
        FILTER (
            'PowerBI structure',
            'PowerBI structure'[Date] = MIN ( 'PowerBI structure'[Date] )
        )
    )
VAR mindate =
    CALCULATE (
        MIN ( 'PowerBI structure'[Date] ),
        ALLSELECTED ( 'PowerBI structure'[Date] )
    )
VAR firstrecord =
    CALCULATE (
        MAX ( 'PowerBI structure'[Adjusted close] ),
        FILTER (
            ALLSELECTED ( 'PowerBI structure' ),
            'PowerBI structure'[Date] = mindate
        )
    )
RETURN
    DIVIDE ( a - [firstrecord], [firstrecord] )

    Here is the output.

    In addition, if you want to only calculate the APPL change, you could try the measure below.

    AAPL =
VAR mindate =
    CALCULATE (
        MIN ( 'PowerBI structure'[Date] ),
        FILTER (
            ALLSELECTED ( 'PowerBI structure' ),
            'PowerBI structure'[Ticker] = "AAPL"
        )
    )
VAR firstrecord =
    CALCULATE (
        MAX ( 'PowerBI structure'[Adjusted close] ),
        FILTER (
            ALLSELECTED ( 'PowerBI structure' ),
            'PowerBI structure'[Date] = mindate
                && 'PowerBI structure'[Ticker] = "AAPL"
        )
    )
VAR a =
    CALCULATE (
        MAX ( 'PowerBI structure'[Adjusted close] ),
        FILTER (
            'PowerBI structure',
            'PowerBI structure'[Date] = MIN ( 'PowerBI structure'[Date] )
        )
    )
RETURN
    DIVIDE ( a - firstrecord, firstrecord )

    Best  Regards,

    Cherry

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks, v-piga-msft. This almost works perfectly. Unfortunately, when i show multiple stocks simultanously, they don't start at the same place (at 0%). Could it be that the base "adjusted close" is tied to one stock vs for individual stocks?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        v-piga-msft , i think i cracked it, it was just one extra step :). Thank you so much for your help! I've just introduced a new variable and used the SELECTEDVALUE function . After that i've just used the variable as a filter in the firstrecord variable (as that unfilters with the ALLSELECTED function).

         

        Thanks again for your help!

         

        Change% = 
VAR a =
    CALCULATE (
        MAX ( 'Ticker_table'[Adjusted close] ),
        FILTER (
            'Ticker_table',
            'Ticker_table'[Date] = MIN ( 'Ticker_table'[Date] )
        )
    )
VAR mindate =
    CALCULATE (
        MIN ( 'Ticker_table'[Date] ),
        ALLSELECTED ( 'Ticker_table'[Date] )
    )
VAR ticker =
    CALCULATE(
        SELECTEDVALUE(Ticker_table[Ticker])
     )

VAR firstrecord =
    CALCULATE (
        MAX ( 'Ticker_table'[Adjusted close] ),
        FILTER (
            ALLSELECTED ( 'Ticker_table' ),
            'Ticker_table'[Date] = mindate),
        Ticker_table[Ticker] = ticker
    )
RETURN
    DIVIDE ( a - firstrecord, firstrecord )