Forum Discussion

JSiebrecht's avatar
JSiebrecht
Icon for Resolver I rankResolver I
4 years ago

Rolling average for X entries

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 day...
Need Help
JSiebrecht's avatar
JSiebrecht
Icon for Resolver I rankResolver I
4 years ago

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's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

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's avatar
    JSiebrecht
    Icon for Resolver I rankResolver I
    4 years ago

    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.