Power BI - Problem creating Moving Average (600k Rows)

user_9911685 0
Viewed 569

I tried to calculate a moving average over the last 3 observations by using an index. Neither the use of "earlier"

Moving_Average = 
calculate(
    sum([var1]) / sum([var2]), 
    filter(
        'table', 
        earlier('table'[Index]) > 'table'[Index]-3 
        && earlier('table'[Index]) <= 'table'[Index]
    )
)

nor the introduction of a variable helped.

Moving_Average2 = 
var thisindex = 'table'[Index]

return
calculate(
    sum([var1]) / sum([var2]), 
    filter( 
        'table', 
        'table'[Index] > thisindex-3 
        && 'table'[Index] <= thisindex
    )
)

Power BI tells me it's working on it but even after 1 hour, it's not done. The problem might be the size of the table with ~600k rows. Is there a way to do it in a timely manner?

powerbi moving-average
1 Answers

Check this out! Create a measure with below DAX.

Moving Average = 
VAR currentIndex = MAX(Data[Index])
VAR movingAverage = CALCULATE(SUM(Data[Column1]), FILTER(ALLSELECTED(Data), Data[Index] > currentIndex - 3 && Data[Index] <= currentIndex))
return movingAverage

enter image description here

user_80430860
Related