Rolling Average within Groups in Power Query Help

[wallace13]

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:

Group	Month	Count	Rolling 3 month Average
A	January	178
A	February	135
A	March	192	168.3333
A	April	157	161.3333
A	May	131	160
B	January	188
B	February	117
B	March	145	150
B	April	117	126.3333
B	May	125	129

NOTE I do not need the months without a N month average (so instances where there are less than N months) I've just included this to help explain the example.

One way to do this would be to join the database on itself multiple times (N-1 times) and compute the average over the Count columns created for each N-X month period created (N month, N-1 Month and N-2 Month...).  However, I believe there must be a better way to do this.

Any ideas? 

Unfortunately, this has to be in M and not DAX since this result will be used in some other computations that require M.

[v-yuta-msft]

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

[Seward12533]

Not sure about the "M" solution but can clarify what you need to in M in the subsequent steps that you can't do in DAX?   Using DAX you can build quite complicated tables you can then link to and use in your model. 

[wallace13]

Hi Seward,

Thank you for your response.

In the subsequent steps I will ultimately need to perform some matrix multiplication and division as I am computing regressions for forecasting purposes (so I will need to calculate the inverse of a square matrix which doesn't seem to be something you can do in DAX).

I'm still at the exploratory stage and realistically I may not be able to do what I want just in Power Query and may still end up with a hybrid with Excel.

Thanks!

[Seward12533 · Accepted solution]

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.