Forum Discussion

Termigez's avatar
Termigez
Regular Visitor
6 years ago
Solved

AVERAGEX over time intervals

Hello everyone, I want to create a new calculated column that will iterate through my rows and will return an average value for current and past 2 values for each unique name taking into consideratio...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Termigez 

    You first need to create an Index for the [Time], so you can look back for a range, because Time type is not supported 

     

    Index = RANKX('Table',[Time],,ASC,Dense)

Result = CALCULATE(AVERAGE('Table'[Amount]),FILTER('Table',[Name]=EARLIER('Table'[Name])),FILTER('Table','Table'[Index]<=EARLIER('Table'[Index])&&'Table'[Index]>EARLIER('Table'[Index])-3))

     

     

     

    Paul Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Termigez 

You first need to create an Index for the [Time], so you can look back for a range, because Time type is not supported 

 

Index = RANKX('Table',[Time],,ASC,Dense)

Result = CALCULATE(AVERAGE('Table'[Amount]),FILTER('Table',[Name]=EARLIER('Table'[Name])),FILTER('Table','Table'[Index]<=EARLIER('Table'[Index])&&'Table'[Index]>EARLIER('Table'[Index])-3))

 

 

 

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.