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 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#M284573 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!

 

 

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

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    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