Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rolling average without dates

I have a dataset that contains batch numbers and cycle times for each batch number. I am trying to create a rolling average over the last 5 batches to plot. Those are the only two variables - I do no...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I found out how to do it minutes after I posted on the forum. I found a calculated column code which works exactly how I want it. 

    Column = 
var my_index = 'Table'[Index]
var my_result = 
    AVERAGEX(
        FILTER(
            'Table',
            'Table'[Index] > my_index-5 &&
            'Table'[Index] <= my_index
        ), 'Table'[Parameter]
    )
RETURN my_result

    This makes a rolling average of the last 5 parameters. You will need to create an index column, but that's the only thing you should need to create in order to graph a rolling average.  

amitchandak's avatar
amitchandak
Super User
6 years ago

Can you create a rank column and use?

Rank = rank(all(table),batch,,desc,dense)

 

last 3 period =

var _max =maxx(table,table[rank])

var _min=maxx(table,table[rank])-3

calculate(sum(table[qty]), filter(all(table),table[rank]>= _min && table[rank]>=_max ))

 

But move batch to a different table and then do it

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin