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 )
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 )