Forum Discussion
DoctorYSG
Helper III
1 year agoDAX for comparing time series data
I have seen some DAX for doing Pearson's Correlation, but it is not really designed for comparing two time series. What would you suggest for two columns (same table) with values that look highly ...
- 1 year ago
Here is my rolling Pearsons Correlation Coefficient solution:
CorrTable = ADDCOLUMNS( SUMMARIZE( 'Traces', 'Traces'[CorrID], 'Traces'[DateStamp] ), "OutlookLatency", CALCULATE( AVERAGE('Traces'[ResponseTime]), 'Traces'[AppName] = "Microsoft Outlook" ), "TokenLatency", CALCULATE( AVERAGE('Traces'[ResponseTime]), 'Traces'[AppName] = "AAD Token Broker Plugin" ), "CredentialLatency", CALCULATE( AVERAGE('Traces'[ResponseTime]), 'Traces'[AppName] = "Credential Manager UI Host (Windows)" ), "EntraLatency", CALCULATE( AVERAGE('Traces'[ResponseTime]), 'Traces'[AppName] = "Microsoft Entra" ) ) TokenCorr = VAR cTable = FILTER( ADDCOLUMNS( VALUES('CorrTable'[CorrID]), "X", CALCULATE(AVERAGE('CorrTable'[OutlookLatency])), "Y", CALCULATE(AVERAGE('CorrTable'[TokenLatency])) ), AND( NOT (ISBLANK([X])), NOT (ISBLANK([Y])) ) ) VAR Count_Items = COUNTROWS(cTable) VAR Sum_X = SUMX(cTable,[X]) VAR Sum_X2 = SUMX(cTable,[X] ^ 2) VAR Sum_Y = SUMX(cTable,[Y]) VAR Sum_Y2 =SUMX(cTable,[Y] ^ 2) VAR Sum_XY =SUMX(cTable,[X] * [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) VAR TokenCorr = DIVIDE( Pearson_Numerator, Pearson_Denominator ) RETURN TokenCorr
DoctorYSG
Helper III
1 year agoI will post my rolling correlations coeficients when it is done.
DoctorYSG
Helper III
1 year agoHere is my rolling Pearsons Correlation Coefficient solution:
CorrTable =
ADDCOLUMNS(
SUMMARIZE(
'Traces',
'Traces'[CorrID],
'Traces'[DateStamp]
),
"OutlookLatency", CALCULATE(
AVERAGE('Traces'[ResponseTime]),
'Traces'[AppName] = "Microsoft Outlook"
),
"TokenLatency", CALCULATE(
AVERAGE('Traces'[ResponseTime]),
'Traces'[AppName] = "AAD Token Broker Plugin"
),
"CredentialLatency", CALCULATE(
AVERAGE('Traces'[ResponseTime]),
'Traces'[AppName] = "Credential Manager UI Host (Windows)"
),
"EntraLatency", CALCULATE(
AVERAGE('Traces'[ResponseTime]),
'Traces'[AppName] = "Microsoft Entra"
)
)
TokenCorr =
VAR cTable =
FILTER(
ADDCOLUMNS(
VALUES('CorrTable'[CorrID]),
"X", CALCULATE(AVERAGE('CorrTable'[OutlookLatency])),
"Y", CALCULATE(AVERAGE('CorrTable'[TokenLatency]))
),
AND(
NOT (ISBLANK([X])),
NOT (ISBLANK([Y]))
)
)
VAR Count_Items = COUNTROWS(cTable)
VAR Sum_X = SUMX(cTable,[X])
VAR Sum_X2 = SUMX(cTable,[X] ^ 2)
VAR Sum_Y = SUMX(cTable,[Y])
VAR Sum_Y2 =SUMX(cTable,[Y] ^ 2)
VAR Sum_XY =SUMX(cTable,[X] * [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)
VAR TokenCorr =
DIVIDE(
Pearson_Numerator,
Pearson_Denominator
)
RETURN TokenCorr