cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pbix1
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

 

Untitled.jpgUntitled1.jpg

1 ACCEPTED SOLUTION
HotChilli
Super User
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

View solution in original post

2 REPLIES 2
HotChilli
Super User
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

View solution in original post

pbix1
Frequent Visitor

Hi HotChilli

 

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

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.