Rolling average for X entries

[JSiebrecht]

Rolling average NOT on dates

Dear community,

I am trying to calculate a rolling/moving average across the last e.g. 100 entries.
But all tutorials I found so far were always on a number of days and the entries in the period of these x days.
I need to calculate the rolling average simply across the latest X values though. Not day or month or years.

I have a table (simplified) with an ID in strictly accending integers (with gaps though) and a duration (integer seconds).
I want to calculate in a measure the moving average of the last X (in the example 10) enties, i.e. the last X entries with the highest ID including the current.

As always
THANK YOU VERY MUCH
for your help!

Best,
Jan

PS
Somehow the HTML table gets reformated very narrowly.
I enclose also a picture of the table.

ID	Duration	SMA
446313784144	87	71,50
446313759838	69	76,20
446313759259	3	81,40
446313756944	49	93,50
446313755556	39	99,70
446313752431	166	107,20
446313752199	4	100,50
446313751968	3	110,40
446313751042	154	119,80
446313749537	141	113,70
446313748727	134	111,10
446313747222	121	111,10
446313746875	124	110,90
446313746065	111	118,90
446313745718	114	117,20
446313744676	99	114,30
446313744444	103	111,70
446313743750	97	107,60
446313742940	93	105,10
446313741667	115	102,90
446313739815	134	100,30
446313738079	119	96,70
446313736574	204	93,40
446313735185	94	80,60
446313734144	85	77,70
446313732755	73	75,00
446313731481	62	72,10
446313681134	72	78,70
446313638079	71	84,10
446313633565	89	89,00
446313632523	98	90,80
446313631134	86	91,00
446313629977	76	90,10
446313628704	65	91,50
446313627894	58	94,44
446313626273	44	99,00
446313624653	128	106,86
446313623380	126	103,33
446313622569	120	98,80
446313621065	107	93,50
446313619792	100	89,00
446313618750	77	83,50
446313618403	90	90,00

[v-jayw-msft]

Hi @JSiebrecht ,

You could add index column in Power Query Editor.

Then create a measure like below.

Measure = CALCULATE(AVERAGE('Table'[Duration]),FILTER(ALLSELECTED('Table'),'Table'[Index]<SELECTEDVALUE('Table'[Index])+10&&'Table'[Index]>=SELECTEDVALUE('Table'[Index])))

Best Regards,

Jay

[JSiebrecht]

Dear @v-jayw-msft 

many thanks for your input.

While this as a formula seems plausible, in my case it will just not evaluate.
The amount of records simply seems to be too large to calculate this in a meaure.
Trying to put it into a custom column gives me calculated SMA values that make no sense at all.

Oh, well. I guess my business user will just have to make due without a moving average and look at the timeseries as is.

Thanks anyway y'all!

[johnt75]

Rolling average =
var currentID = SELECTEDVALUE( 'Table'[ID] )
return AVERAGEX( TOPN(10, FILTER( ALL('Table'), 'Table'[ID] <= currentID), 'Table'[ID] ), 'Table'[SMA] )

[JSiebrecht]

Dear @johnt75 ,

thanks for the prompt response!
I tried your suggestion, but my machine is not able to finish the evaluation and after much CPU cooking stops for lack of memory. And I have only 24'000 records.

I tried without the ALL() and then the memory is sufficient.
But I do not get a moving average. Each item shows the value of Duration also for the moving average.

DAX used:

Import Duration Moving Average = 
 var currentID = SELECTEDVALUE( Contract[ID] )
return 
AVERAGEX(
    TOPN(10, FILTER(ALL('Contract'), Contract[ID] <= currentID), Contract[ID]),
    Contract[Import Duration] )

This is the result:

Any idea?

Thanks,
Jan

[johnt75]

Unfortunately the ALL is required to get the correct result, that removes the row context introduced by the AVERAGEX. Without it it will only consider the current row, which is why you are seeing the same value in the moving average as in the import duration.

Do you need the averages for every row you have, or only the most recent? I'm thinking that you could use the same basic code to produce a calculated table, which would only be calculated once during data refresh, that had only the most recent X values of id and included the moving average as a column rather than a measure. You could even try adding the column to your full table and see if it can manage that, 24,000 rows isn't so many.

[JSiebrecht]

Hi @johnt75 

I understand.
Didn't think 24000 recods would be a problem. I saw guys do rolling averages with stock datasets of several years without problems. In the examples on the net, as said earlier, they usually use dates and the DATESBETWEEN function or DAYSINPERIOD or so.
Apparently this is easier on memory than the TOPN.

I tried with the ALL() and write the Moving Average to a custom column rather than a measure.
But thenn I don't get any entries at all and the column stays empty.