Dynamic Correlation function

[Anonymous]

Hi all

I have 2 time series I wish to compare and I've been used to using the correl() function in Qlikview. Unfortunately I've not seen one that is dynamic on the PowerBI forums or perhaps I've misinterpreted ones I've tried out. I'm not after a correlation matrix but merely an ability to have two time series and a correlation that changes as the period/date changes on them too. I wanted to display this correlation measure as a dynamic kpi. 

Has anyone done something like this?

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

Here is a good article about Correlation, Seasonality and Forecasting with Power BI. Could you go to check if it helps in your scenario?

If you still have any questions, could you post your table structures with some sample/mock data, and the expected result? So that we can better assist on this issue. 

Regards

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

Here is a good article about Correlation, Seasonality and Forecasting with Power BI. Could you go to check if it helps in your scenario?

If you still have any questions, could you post your table structures with some sample/mock data, and the expected result? So that we can better assist on this issue. 

Regards

[Anonymous · Accepted solution]

Thanks I will have a look.

I've attached a file with two time series (X and Y) across a number of months. In excel if you do a correl( ) function which I've done in cell D2 then you get 0.61

I was doing stuff like this on a dynamic KPI tile in Qlikview and Qlik Sense. I wanted to determine how to do this same thing with PowerBI. So I'm not after a correlation matrix file, simply the measure that will dynamically create this value for me when I filter different fields and different time periods

thanks

Google Drive link to XLS file

[Anonymous]

I just checked that link and it was the closest of the solutions I've found on here so far. I got it working on my data but it calculates things undynamically... I tried making it all work based on measures instead of columns but either way the results were static and did not change based on filtering fields or dates.

I will try it again though as I tweaked a few things smoupre wrote and thought I was getting somewhere but ran out of time.

will advise if I solve this

[Anonymous]

@v-ljerr-msft solution led me down the right path with this... the link had its own link to  http://www.mathsisfun.com/data/correlation.html which worked. As a reminder, the @Greg_Deckler link was fine but this set the correlation to be non-dynamic when you changed periods. Recalculating it all to be based on measures worked perfectly though. Best approach for anyone new to this is to create all the measures in their separate table, eg Table A and Table B and to test as you go along, then try to consolidate all the code into smaller or even a single measure if you want (for neatness purposes that is).