Forum Discussion

pbix1's avatar
pbix1
Resolver II
4 years ago
Solved

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

 

Need Help
  • HotChilli's avatar
    HotChilli
    4 years ago

    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

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    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

    • pbix1's avatar
      pbix1
      Resolver II
      4 years ago

      Hi HotChilli

       

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