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 ,

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 )