Forum Discussion

DoctorYSG's avatar
DoctorYSG
Icon for Helper III rankHelper III
1 year ago
Solved

DAX 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 ...
  • DoctorYSG's avatar
    DoctorYSG
    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

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi DoctorYSG ,

Accepting the helpful reply as solution may help some community members in the future who may come across this post.
You could always reopen this thread or create a new one later.

Thank you.

DoctorYSG's avatar
DoctorYSG
Icon for Helper III rankHelper III
1 year ago

I will post my  rolling correlations coeficients when it is done.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi DoctorYSG ,

    Thank you for the update, we are going to keep this thread open as per your request.

    For any further discussions or questions, please post in this post or create a new post in the Microsoft Fabric Community Forum,  we’ll be happy to assist.

    Thank you for being part of the Microsoft Fabric Community.

     

  • DoctorYSG's avatar
    DoctorYSG
    Icon for Helper III rankHelper III
    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