Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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,
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,
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,
Perfect! This works. How do you make the 60 value dynamic to take in user values from a slicer e.g 20,30,60 ?
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |