Calculate moving average of continuous or overlapping values

[Jo5hua22]

Hi,

I would like to calculate the Moving Average of 3 continuous or overlapping values. 

An example is given in the below screeshot

I know it has to deal with sorting the dates according to index value, then using only the respective batches and finally calculaing the moving average of those batches that are sorted according to the dates.

Can you please help me out? 

Example:

[Jo5hua22]

Hi @v-deddai1-msft ,

In addition to your solution, I need to rank the table based on another column if there are more than 1 value of the same batch number are in the same date. Can you let me know how to do it?

Please note that the required calculation should cover your previous solution wherein here we sort the batches  and now if there are more than 1 value produced on the same date then loop number has to be taken into consideration.

Example:

[Jo5hua22]

Thank you very much for the help

[v-deddai1-msft · Accepted solution]

Hi @Jo5hua22 ,

You need to create a index column in your table and rank your table by the index column and date column:

rankvalue = RANKX(FILTER('Table','Table'[Batch Number] = EARLIER('Table'[Batch Number])&&NOT(ISBLANK('Table'[Date]))),VALUE('Table'[Date])+'Table'[Index]/100,,ASC)

Then you can use the following calculated column to compute the moving average:

moving average = VAR A = 'Table'[rankvalue] RETURN  IF('Table'[rankvalue]>=3,AVERAGEX(FILTER(ALL('Table'),'Table'[Batch Number] = EARLIER('Table'[Batch Number])&& 'Table'[rankvalue]<=A&&'Table'[rankvalue]>A-3),'Table'[Value]),'Table'[Value])

Please refer to the pbix file.

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

Best Regards,

Dedmon Dai