Forum Discussion

Greg25's avatar
Greg25
New Member
4 years ago
Solved

Normalize time series data for display in Line Chart

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 visualiz...
Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    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's avatar
Greg25
New Member
4 years ago

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's avatar
mahoneypat
Microsoft Employee
4 years ago

Glad it worked.  Updated with ALLSELECTED.

Pat