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

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
Icon for Resident Rockstar rankResident 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 )