Normalize time series data for display in Line Chart

[Greg25]

Hi all,

I would like to display two time series in a normalized way to see their performance relative to each other. Think about two stocks (e.g. Apple vs. Microsoft) and I would like to visualized which stock performs better in this year. 

An example what I look for

The "problem" is that each time I change the date range, for which the data is displayed, the normalization to zero percent has to be recalculated since it depends on the visual. 

My tables and model look like this

         

Please find the full model and data at https://drive.google.com/file/d/1dU_ZGyb9QjeWbM-etW83Vq9D_l-ljMr5/view?usp=sharing

My problem is somehow similar as https://community.powerbi.com/t5/Desktop/Normalize-time-series-data-to-first-non-zero/m-p/598559#M28... from @Anonymous but the solution doesn't work for my case.

I tried (as newbie) myself to create a line chart based on the above mentioned solution but I cannot get it to work despite using around 20 hours for it. My code looks like this but isn't working.

ChangeIndex% = 

VAR a =
    CALCULATE (
        MAX ( 'IndexData'[index1_close] ),
        FILTER (
            'IndexData',
            'IndexData'[DateIndex] = MIN ( 'IndexData'[DateIndex]  )
        )
    )
VAR mindate =
    CALCULATE (
        MIN ( 'IndexData'[DateIndex]  ),
        ALLSELECTED ( 'IndexData'[DateIndex]  )
    )

VAR firstrecord =
    CALCULATE (
        MAX ( 'IndexData'[index1_close]),
        FILTER (
            ALLSELECTED ( 'IndexData' ),
            'IndexData'[DateIndex] = mindate)
    )
RETURN
    DIVIDE ( a - firstrecord, firstrecord )

My two problems with the code are

1. The calculation delivers the wrong percentage.
2. If I would use this code twice (for each index), I would always get a circular dependency that I cannot resolve. 

Any help would be appreciated! Thank you!

[mahoneypat · Accepted solution]

I looked at your file.  A few comments

• You should use measures where possible instead of calculated columns
• You should change the relationship to be a single-direction and 1:M
• You should unpivot your data to make your analysis easier (i.e., your index1 and index2 values should be in the same column with a second column with the values of index1 and index2). That way you can use that second column as the legend in your visuals.
• Below is an example measure for Index1 (I couldn't change your query to unpivot your data, so two measures still needed)
• Attached is the modified pbix file, if useful

Rel Change 1 =
VAR vThisValue =
    MAX ( IndexData[index1_close] )
VAR vFirstDate =
    CALCULATE ( MIN ( IndexData[DateIndex] ), ALLSELECTED ( IndexData[DateIndex] ) )
VAR vFirstClose =
    CALCULATE (
        MAX ( IndexData[index1_close] ),
        ALL ( 'Date' ),
        IndexData[DateIndex] = vFirstDate
    )
RETURN
    DIVIDE ( vThisValue - vFirstClose, vFirstClose )

Pat

[Greg25]

Thank you so much, Pat.

Great solution, thank you so much! I just found one little thing that needs to be changed from your script. In your script, when I change the data it does not normalize to zero to the new data but it normalizes to the first date (end of 2019).

Original version:

With a little change, I get the desired result:

The change from PAT's DAX is as follows:

Rel Change 1 = 
VAR vThisValue =
    MAX ( IndexData[index1_close] )
VAR vFirstDate =
    CALCULATE ( MIN ( IndexData[DateIndex] ), ALLSELECTED( IndexData[DateIndex] ) )
VAR vFirstClose =
    CALCULATE (
        MAX ( IndexData[index1_close] ),
        ALL ( 'Date' ),
        IndexData[DateIndex] = vFirstDate
    )
RETURN
    DIVIDE ( vThisValue - vFirstClose, vFirstClose )

The Change is from 

ALL

to 

ALLSELECTED

@mahoneypat: Could you, please, update your post with this change so that people later find this change in the accepted solution?



 

[mahoneypat]

Glad it worked.  Updated with ALLSELECTED.

Pat