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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Termigez 

Try one of the two as columns

Avg  = avergageX(filter(table,table[Name] =earlier(table[Name]) && table[time] <=earlier(table[time])),table[Amount])

Avg  = avergageX(filter(table,table[Name] =earlier(table[Name]) && table[time] <earlier(table[time])),table[Amount])
Termigez's avatar
Termigez
Regular Visitor
6 years ago

Unfortunately those formulas don't work. I would appreciate any other ideas.