cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

## Rolling Average Using Index

Hi

I am trying to create a rolling average in DAX using method 1 described at https://www.youtube.com/watch?v=UcB-Cz6v6Sw but by applying it to my own situation. Basically, I'm after a generic method of creating rolling averages based on an index column. I don't want to use dates as I often work with period numbers etc. and am after a solution I can apply to different situations. I have created an index column as shown below in the first screenshot. I then tried to adapt the method described in the link to my own table but it doesn't work. It just replicates the order quantity column for the last 3 rows in the table. (I have sorted it in descending order). Can anyone help adapt the DAX so it works for my table? Many thanks.

Index = COUNTROWS(filter(SalesByOrderDateKey,SalesByOrderDateKey[Sales_OrderDateKey]<=earlier(SalesByOrderDateKey[Sales_OrderDateKey])&&SalesByOrderDateKey[Group]=earlier(SalesByOrderDateKey[Group])))

Rolling Average =
var X1 = max(SalesByOrderDateKey[Index])
var X2 = max(SalesByOrderDateKey[Index])-1
var X3 = max(SalesByOrderDateKey[Index])-2
var Y1 = calculate(distinctcount(SalesByOrderDateKey[Index]),SalesByOrderDateKey[Index] in {X1,X2,X3})
var Y2 = SalesByOrderDateKey[Order Quantity]
return
calculate(Y2,SalesByOrderDateKey[Index] in {X1,X2,X3})/Y1

1 ACCEPTED SOLUTION
Super User

Normally this would be done with a measure but if you want a column...

``````Rolling Average =
var X1 = SalesByOrderDateKey[Index]
var X2 = SalesByOrderDateKey[Index]-1
var X3 = SalesByOrderDateKey[Index]-2
var Y1 = calculate(distinctcount(SalesByOrderDateKey[Index]), FILTER(SalesByOrderDateKey, SalesByOrderDateKey[Index] in {X1,X2,X3}))

RETURN

DIVIDE(calculate(SUM(SalesByOrderDateKey[Order Quantity]), FILTER(SalesByOrderDateKey, SalesByOrderDateKey[Index] in {X1,X2,X3})), Y1)``````

The code that didn't work would always return the same values for the first 3 variables.  After that Y1 would always return zero.

Let me know how it goes

2 REPLIES 2
Super User

Normally this would be done with a measure but if you want a column...

``````Rolling Average =
var X1 = SalesByOrderDateKey[Index]
var X2 = SalesByOrderDateKey[Index]-1
var X3 = SalesByOrderDateKey[Index]-2
var Y1 = calculate(distinctcount(SalesByOrderDateKey[Index]), FILTER(SalesByOrderDateKey, SalesByOrderDateKey[Index] in {X1,X2,X3}))

RETURN

DIVIDE(calculate(SUM(SalesByOrderDateKey[Order Quantity]), FILTER(SalesByOrderDateKey, SalesByOrderDateKey[Index] in {X1,X2,X3})), Y1)``````

The code that didn't work would always return the same values for the first 3 variables.  After that Y1 would always return zero.

Let me know how it goes

Frequent Visitor

Hi HotChilli

Thank you very much. This works. Exactly what I wanted.

## Helpful resources

Announcements

#### Launching new user group features

Learn how to create your own user groups today!

#### Check it Out!

Click here to read more about the November 2021 Updates!

#### Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors