How it works...

To calculate the DeSeasonal Correlation Coefficient measure, we start by creating the table variable, __SeasonalTable. This is the table that we will use to compute our seasonality. We get ALL of the rows in the R08_Sales table using SELECTCOLUMNS to rename our Sales column X. Then, we use ADDCOLUMNS to add our Y column, which is the sum of all the related rows in R08_Wages. Finally, we use FILTER to filter out any rows where YearQuarter is blank. This accounts for having more unbalanced tables.

Now, we start accounting for seasonality. We do this by computing the average of sales for each quarter (__SeasonQ1Avg, __SeasonQ2Avg, __SeasonQ3Avg, and __SeasonQ4Avg), as well as the overall average (__SeasonAverage). Then, we divide each quarter's average by __SeasonAverage and store these values in the seasonal index variables, that is, __SeasonQ1SI, __SeasonQ2SI, __SeasonQ3SI, and __SeasonQ4SI.

Now, we can calculate __CorrelationTable by adding the Deseasonal_X column to __SeasonalTable using ADDCOLUMNS. The calculation for the Deseasonal_X column simply checks the value of the Quarter column for each row and, depending on the value of the Quarter column, it divides the X column by either __SeasonQ1SI, __SeasonQ2SI, __SeasonQ3SI, or __SeasonQ4SI. The Deseasonal_X column now contains the de-seasonalized values for our sales.

Now, we can calculate the parts of Pearson's Correlation Coefficient, __Count, __SumX, __SumX2, __SumY, __SumY2, and __SumXY. However, when computing our values for __SumX, __SumX2, and __SumXY, instead of using our X column, we substitute our Deseasonal_X column. By doing this, we can simply plug __Count, __SumX, __SumX2, __SumY, __SumY2, and __SumXY into the formula for Pearson's Correlation Coefficient to compute __r and then return __r as the value for our measure, that is, DeSeasonal Correlation Coefficient.

The calculation for the DeSeasonal Sales measure is nearly identical to the one for the DeSeasonal Correlation Coefficient measure, except we return our Deseasonal_X values. This measure is present simply for display purposes within the Line chart visualization.