DAX Performance Issue - 7 days moving average over 100 years of data

[Sergii24]

Hi all,

In my dataset there are 2 tables: fact table "Fact" (31k rows) and "DateTable" (marked as date table and has 46K rows). I have data on daily level starting from 1896 until 2021. My objective is to calculate 7 days moving average of column "Close", considering that there are empty spaces in the "Fact table" (dates in this table are not consecutive).

However, I have difficulties way earlier I get to this calculation. When I write one of the simpliest DAX expression such as the one below, it takes minutes to compute it and sometimes I get an error saying that there is no enough memory to compute it!

Test = 
VAR TestDate = MIN( DateTable[Date] )
RETURN
    CALCULATE(
        AVERAGE( Fact[close] ),
        DateTable[Date] < TestDate
    )

What am I doing wrong? I don't belive that Power BI has difficulties in such calculation over 30k rows. Am I wrong?
Link to the file is here.

Thanks!

[CNENFRNL · Accepted solution]

7 DAYS MA =
CALCULATE (
    AVERAGE ( 'Fact'[close] ),
    DATESINPERIOD ( DateTable[Date], MAX ( DateTable[Date] ), -7, DAY )
)