Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Rolling Average within Groups in Power Query Help

Hi! I'm trying to figure out how to add a column which will compute a rolling N month average within groups in M.   For example, I would like to have something similar to this table:   Grou...
Need Help
  • Seward12533's avatar
    Seward12533
    8 years ago

    Dont' forget PowerPivot in Excel where you have the same PowerQuery [M] and DAX resources within PowerBI. But you can put results in a table on a sheet and then run wild with macros. Not sure if the R integration will help you in PowerBI but you might look into that and from the recent MS BI Summit I learned that soon you will be able to use python scripts which will open up all kinds of options in PowerBI.

     

    If you don't see PowerPivot in excel menus you just need to enable it assuming you using Pro or other version that includes powerpivot. Tip - Its a COM add in.

v-yuta-msft's avatar
v-yuta-msft
Community Support
8 years ago

Hi wallace13,

 

It's not easy to write loop or recursion in Power Query, DAX is very useful in your senario, so I would recommend you to create a calculate column using DAX as below:

Rolling 3 month Average = 
VAR Start_Index = Table1[Index] - 2
VAR End_Index = Table1[Index]
RETURN
    IF (
        COUNTROWS (
            FILTER (
                ALL ( Table1 ),
                Table1[Group] = EARLIER ( Table1[Group] )
                    && Table1[Index] >= Start_Index
                    && Table1[Index] <= End_Index
            )
        )
            < 3,
        BLANK (),
        CALCULATE (
            AVERAGE ( Table1[Count] ),
            FILTER (
                ALL ( Table1 ),
                Table1[Group] = EARLIER ( Table1[Group] )
                    && Table1[Index] >= Start_Index
                    && Table1[Index] <= End_Index
            )
        )
    )

 

 

Regards,

Jimmy Tao