Correlation between same column, different items for time periods

ElliotP

In this sort of "pairwise comparison" scenario, where you have multiple entities in the same table (in this case distinguished by StockSymbolCurrency) I would normally follow the below steps.

My modifed copy of your pbix is here:
https://www.dropbox.com/s/y9h2ncitj46ee6a/PowerBiForumExample2%20Owen%20edit.pbix?dl=0

1. Create a copy of the entity dimension table, in your case a copy of ReferenceTable which I would call ReferenceTableComparison
2. Create a relationship between StockBarDatExample and ReferenceTableComparison, but make it inactive
3. Create the appropriate value measure that will be used for the company selected in ReferenceTable. For testing purposes I created
   

   Average Close = 
   AVERAGE ( StockBarDatExample[close] )

4. Create the same measure for the Comparison Company, which activates the inactive relationship, and clears the filter on ReferenceTable:
   

   Average Close Comparison =
   CALCULATE (
       [Average Close],
       ALL ( ReferenceTable ),
       USERELATIONSHIP ( StockBarDatExample[StockSymbolCurrency], ReferenceTableComparison[StockSymbolCurrency] )
   )

5. You can then selected Company & Comparison Company using slicers, and use Average Close & Average Close Comparison together in visuals.
6. The Pearson Correlation Coefficient can be calculated using a method similar to that used here. This relies on having the above two measures set up.
   Here is the measure I tested with your data:
   

   Pearson Correlation Coefficient = 
   VAR DateTimes =
       // Create a table of date/times where both stocks have a Close value
       FILTER (
           SUMMARIZE ( StockBarDatExample, DateTable[DateKey], TimeTable[Column1] ), // Date & Time columns
           AND (
               NOT ( ISBLANK ( [Average Close] ) ),
               NOT ( ISBLANK ( [Average Close Comparison] ) )
           )
       )
   // Construct table of pairs of Close values
   VAR Known =
       SELECTCOLUMNS (
           DateTimes,
           "Known[X]", [Average Close],
           "Known[Y]", [Average Close Comparison]
       )
   // Calculate correlation coefficient
   VAR Count_Items =
       COUNTROWS ( Known )
   VAR Average_X =
       AVERAGEX ( Known, Known[X] )
   VAR Average_X2 =
       AVERAGEX ( Known, Known[X] ^ 2 )
   VAR Average_Y =
       AVERAGEX ( Known, Known[Y] )
   VAR Average_Y2 =
       AVERAGEX ( Known, Known[Y] ^ 2 )
   VAR Average_XY =
       AVERAGEX ( Known, Known[X] * Known[Y] )
   VAR CorrelationCoefficient =
       DIVIDE (
           Average_XY
               - Average_X * Average_Y,
           SQRT ( ( Average_X2 - Average_X ^ 2 ) * ( Average_Y2 - Average_Y ^ 2 ) )
       )
   RETURN
       CorrelationCoefficient

The test report page in the above pbix looks like this:

Hopefully that helps. :)

Regards,

Owen