Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Rolling Average Using Index (NOT DATE)

Dear all, I would like to ask , "What are examples of workable coding that I can try using if I need to perform rolling average of 30 weeks using the INDEX row and NOT Dates?" because based on my re...
Power BI Dax
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    Anonymous So like:

    Better RA = 
    VAR __Index = MAX('Table'[Index])
    VAR __MinIndex = __Index - 30
    VAR __Table = FILTER(ALLSELECTED('Table'),[Index] < __Index && [Index] >= __MinIndex)
RETURN
    AVERAGEX(__Table,[Value])

    Note, I pretty much just replaced [Date] with [Index]...

    Better Running Total - Microsoft Power BI Community

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Dear Mr Greg_Deckler,

How can I modify the DAX formula to match with the calculated rolling average in my Excel data source file as there are differences between the data that I have and in the chart that I created in Power BI?

 

Please advice. Thank you very much.

 

- Kaili

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Anonymous Not the first clue. I don't have any data to compare one with the other so impossible to say.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Dear Mr Greg_Deckler ,

      here are the screenshots of my data (we are looking at row 276's total in Excel and the Rolling Average Sample 1 in Power BI). As you can see, the data does not match. Therefore, I would like to know if there are DAX amendments that I can make to ensure that this does not happen?

      Further advice is much appreciated.

      Yours sincerely,

      Kaili

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

        Anonymous What is your Excel formula for your G276 cell?