Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
Hi @JSiebrecht ,
You could add index column in Power Query Editor.
Then create a measure like below.
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!
Rolling average =
var currentID = SELECTEDVALUE( 'Table'[ID] )
return AVERAGEX( TOPN(10, FILTER( ALL('Table'), 'Table'[ID] <= currentID), 'Table'[ID] ), 'Table'[SMA] )
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
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.
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.
User | Count |
---|---|
86 | |
83 | |
67 | |
61 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
84 | |
70 |