Normalize time series data with multiple per category with date slicer

[danielntamasi]

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% and then go up or down depending on the changes in the stock price over time. The curved ball is that the data should reflect to the date slicer on the page. I've tried to create this but fell short of cracking the nut. 😞

Associated data table:

• [Adjusted close] -- has the price information
• [Date]
• [Ticker] -- has the category information (such as AAPL, ETSY, etc.)

I'm trying to create the hand-drawn visual on the bottom of the pic:

Thank you for your help in advance.

[v-piga-msft]

Hi @danielntamasi ,

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

[danielntamasi]

@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]

Hi @danielntamasi ,

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

[danielntamasi]

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?

[danielntamasi · Accepted solution]

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