Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pbix1
Resolver I
Resolver I

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

Hi HotChilli

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.