Correlation of returns over time but as calculated column

[Anonymous]

Column A = Date, Column B = returns on X stock, Column C = returns on Y stock. The quick measure in PowerBI desktop gives the correlation as a single scalar value taking the correlation between all rows of B and C. 

Is there a way to only look back 60 days from last row date as a calculated column to give me the 60 day historic correlation between X & Y on each row. This way I can chart change in 60 day correlation over time between X&Y. 

Thanks in advance for your help.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can create a calculated column using following formula to meet your requirement:

60 Days =
VAR d = [Date]
VAR Correlation_Table =
    FILTER (
        FILTER (
            ADDCOLUMNS (
                GROUPBY ( 'Table', 'Table'[Date] ),
                "Value_X",
                VAR de = [Date]
                RETURN
                    CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', [Date] = de ) ),
                "Value_Y",
                VAR de = [Date]
                RETURN
                    CALCULATE ( SUM ( 'Table'[Value2] ), FILTER ( 'Table', [Date] = de ) )
            ),
            AND ( NOT ( ISBLANK ( [Value_X] ) ), NOT ( ISBLANK ( [Value_Y] ) ) )
        ),
        [Date] > d - 60
            && [Date] <= d
    )
VAR Count_Items =
    COUNTROWS ( Correlation_Table )
VAR Sum_X =
    SUMX ( Correlation_Table, [Value_X] )
VAR Sum_X2 =
    SUMX ( Correlation_Table, [Value_X] ^ 2 )
VAR Sum_Y =
    SUMX ( Correlation_Table, [Value_Y] )
VAR Sum_Y2 =
    SUMX ( Correlation_Table, [Value_Y] ^ 2 )
VAR Sum_XY =
    SUMX ( Correlation_Table, [Value_X] * [Value_Y] )
VAR Pearson_Numerator = Count_Items * Sum_XY - Sum_X * Sum_Y
VAR Pearson_Denominator_X = Count_Items * Sum_X2 - Sum_X ^ 2
VAR Pearson_Denominator_Y = Count_Items * Sum_Y2 - Sum_Y ^ 2
VAR Pearson_Denominator =
    SQRT ( Pearson_Denominator_X * Pearson_Denominator_Y )
RETURN
    DIVIDE ( Pearson_Numerator, Pearson_Denominator )

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that we have shared.

Best regards,

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can create a calculated column using following formula to meet your requirement:

60 Days =
VAR d = [Date]
VAR Correlation_Table =
    FILTER (
        FILTER (
            ADDCOLUMNS (
                GROUPBY ( 'Table', 'Table'[Date] ),
                "Value_X",
                VAR de = [Date]
                RETURN
                    CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', [Date] = de ) ),
                "Value_Y",
                VAR de = [Date]
                RETURN
                    CALCULATE ( SUM ( 'Table'[Value2] ), FILTER ( 'Table', [Date] = de ) )
            ),
            AND ( NOT ( ISBLANK ( [Value_X] ) ), NOT ( ISBLANK ( [Value_Y] ) ) )
        ),
        [Date] > d - 60
            && [Date] <= d
    )
VAR Count_Items =
    COUNTROWS ( Correlation_Table )
VAR Sum_X =
    SUMX ( Correlation_Table, [Value_X] )
VAR Sum_X2 =
    SUMX ( Correlation_Table, [Value_X] ^ 2 )
VAR Sum_Y =
    SUMX ( Correlation_Table, [Value_Y] )
VAR Sum_Y2 =
    SUMX ( Correlation_Table, [Value_Y] ^ 2 )
VAR Sum_XY =
    SUMX ( Correlation_Table, [Value_X] * [Value_Y] )
VAR Pearson_Numerator = Count_Items * Sum_XY - Sum_X * Sum_Y
VAR Pearson_Denominator_X = Count_Items * Sum_X2 - Sum_X ^ 2
VAR Pearson_Denominator_Y = Count_Items * Sum_Y2 - Sum_Y ^ 2
VAR Pearson_Denominator =
    SQRT ( Pearson_Denominator_X * Pearson_Denominator_Y )
RETURN
    DIVIDE ( Pearson_Numerator, Pearson_Denominator )

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that we have shared.

Best regards,

[Anonymous]

Perfect! This works. How do you make the 60 value dynamic to take in user values from a slicer e.g 20,30,60 ?

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

The measure is dynamic but the calculated columns are computed during the database processing and then stored in the model. So In your scenario, we cannot make the calculated column dynamically.

But we can create a measure used in visual if the x-axis is date. Firstly, We create a What-If Parameter.

Then we can create a measure:

Correlation_Measure = 
VAR d = MAX('Table'[Date])
VAR Correlation_Table =
    FILTER (
        FILTER (
            ADDCOLUMNS (
                GROUPBY ( ALLSELECTED('Table'), 'Table'[Date] ),
                "Value_X",
                VAR de = [Date]
                RETURN
                    CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALLSELECTED('Table'), [Date] = de ) ),
                "Value_Y",
                VAR de = [Date]
                RETURN
                    CALCULATE ( SUM ( 'Table'[Value2] ), FILTER ( ALLSELECTED('Table'), [Date] = de ) )
            ),
            AND ( NOT ( ISBLANK ( [Value_X] ) ), NOT ( ISBLANK ( [Value_Y] ) ) )
        ),
        [Date] > d - [PreviousDaySlicer Value]
            && [Date] <= d
    )
VAR Count_Items =
    COUNTROWS ( Correlation_Table )
VAR Sum_X =
    SUMX ( Correlation_Table, [Value_X] )
VAR Sum_X2 =
    SUMX ( Correlation_Table, [Value_X] ^ 2 )
VAR Sum_Y =
    SUMX ( Correlation_Table, [Value_Y] )
VAR Sum_Y2 =
    SUMX ( Correlation_Table, [Value_Y] ^ 2 )
VAR Sum_XY =
    SUMX ( Correlation_Table, [Value_X] * [Value_Y] )
VAR Pearson_Numerator = Count_Items * Sum_XY - Sum_X * Sum_Y
VAR Pearson_Denominator_X = Count_Items * Sum_X2 - Sum_X ^ 2
VAR Pearson_Denominator_Y = Count_Items * Sum_Y2 - Sum_Y ^ 2
VAR Pearson_Denominator =
    SQRT ( Pearson_Denominator_X * Pearson_Denominator_Y )
RETURN
    DIVIDE ( Pearson_Numerator, Pearson_Denominator )

Best regards,