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 not have access to dates. I have tried to make an index column of dates, and I have tried other solutions that have been presented on this forum for similar situations including this one, to no avail. 

 

Any help would be much appreciated.

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.  

5 Replies

  • 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

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    6 years ago
    Hi Anonymous ,
     
    It is very difficult to analyze without looking at the data and just by imagining. Could you please share sample data or sample pbix?(Please mask any sensitive data before uploading)
     
    Best Regards,
    Liang
    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

    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.  

    • V-lianl-msft's avatar
      V-lianl-msft
      Community Support
      6 years ago

      Hi Anonymous ,

       

      It's glad that you have solved your problem.
      Please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

       

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

    • Jrose's avatar
      Jrose
      Frequent Visitor
      5 years ago

      Hi

      I'm trying to do something similar, but I'm having trouble following your references.  When you create an index column, do you mean that each record within a batch got the same index number?

       

      What do table Index and table Parameter refer to?  Did you create a separate table just for the index numbers and Parameters is the actual data?