circular dependencies on moving average calculated column

[jonasdedual24]

I'm trying to create a moving average my table.

I have tryed below:

MovingAverage_Time_2 = 
VAR currentindex = Query1[Time]
VAR pastindex = currentindex - TIME(0, 10, 0)
VAR calc =  CALCULATE(
    AVERAGEx(Query1, Query1[audcad_close]),
    DATESBETWEEN(
        Query1[Time],
        "01.01.2024",
        "02.01.2024"
    )
)
/*VAR calc =  CALCULATE(
    AVERAGEx(Query1, Query1[audcad_close]),
    DATESBETWEEN(
        Query1[Time],
        pastindex,
        currentindex
    )
)
*/

RETURN calc

Unfortuantely, I get a an error message:

To simplify the error, I have repaced pastindex and currentindex with static values. However I still get same error.

There is no reference to MovingAverage_Time_2 and query1 is the only table in my model.

I've also tried to work with filters:

MovingAverage_Time = 
VAR currentindex = Query1[DateTime]
VAR pastindex = currentindex - TIME(0,[MovingAverage],0)
VAR calc = CALCULATE(
    AVERAGEx(Query1, Query1[audcad_close]),
    FILTER(
        Query1, 
        Query1[DateTime] > pastindex 
        && Query1[DateTime] <= currentindex
    )
)

RETURN calc

The above query works but the performance is not acceptable. I have about 100k rows.

Any help is highly apreciated.

[v-xinruzhu-msft]

Hi @jonasdedual24 
Try the following code

MovingAverage_Time =
VAR currentindex = Query1[DateTime]
VAR pastindex =
    currentindex - TIME ( 0, [MovingAverage], 0 )
VAR _filter =
    FILTER (
        Query1,
        Query1[DateTime] > pastindex
            && Query1[DateTime] <= currentindex
    )
RETURN
    CALCULATE ( AVERAGE ( Query1[audcad_close] ), _filter )

Best Regards!

Yolo Zhu

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