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 me...
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

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.