Forum Discussion

Irwan's avatar
Irwan
Super User
2 years ago
Solved

Need Help on Autocorrelation Calculation

Hello Power BI expert,   I need an advice on how to do autocorrelation calculation in Power BI. for now i am trying to replicate the result from excel tutorial. I read there are two ways of...
"Need Help"
Irwan's avatar
Irwan
Super User
2 years ago

Hello lbendlin 

 

here is the sample data. Basically that is a data came from the website that i mentioned (I am trying to recreate the result before applying it into real data).

Jan56
Feb58
Mar69
Apr50
May66
Jun68
Jul78
Aug73
Sept88
Oct90

 Here is the result that I wish to get in Power BI

 

However, if Power BI has no memory or concept of self-referencing, then it is impossible doing autocorrelation in Power BI?

 

Thank you and really appreciate for the help.

  • Irwan's avatar
    Irwan
    Super User
    2 years ago

    Hello lbendlin 

     

    that DAX is superb, thank you.

    I dont know if there is a sumx offset at line 6 (maybe) as a "bridge" before sumx the whole result as in Line 8 (thought it can be done directly by using earlier indexing).

    I need to work again on my DAX understanding as a beginner.

     

    Again, thank you and really appreciate for the help.

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      As always you can approach the issue with different DAX functions but you will want to unlearn EARLIER and embrace the window functions. Makes the shifted reference so much easier.

      • Irwan's avatar
        Irwan
        Super User
        2 years ago

        hello lbendlin 

         

        sorry in advance, i got another question, is it possible to put the measure (the correlation result) into a new column?

         

        Thank you and appreciate for the help.