moving average of the last 5 days with values

[rmeng]

I want to calculate the moving average of the last 5 days with values, I have started with this measure but it counts the last 5 Days with no values 

I have a table with the result, the problem is that I want to calculate the last 5 days with [AAAA] values so the last row [FirstDayInPeriod] should be 1/4/2022 and not 1/6/2022. 

Data	## FirstDayInPeriod	AAAA	## Moving average 5 days
1/4/2022	12/31/2021	44.98
1/5/2022	1/1/2022	8.96
1/6/2022	1/2/2022	61.05
1/7/2022	1/3/2022	9.77
1/10/2022	1/6/2022	12.62	27.813932

Could you help me changing the measure?

THANKS

[SiemdeKort]

I have created an improved version,

Average based on last 5 dates =

CALCULATE(

    AVERAGE('Table 1'[value]),

    TOPN(5,'Table 1','Table 1'[date],DESC))

accept this as solution if this helped

[SiemdeKort · Accepted solution]

Rankdate =

    CALCULATE(

        COUNTROWS('Table 1'),

        FILTER('Table 1',

            EARLIER('Table 1'[date])<='Table 1'[date])

        )
i created this column to rank the dates so you can filter the last 5

AVERAGEX(

    FILTER('Table 1','Table 1'[Rankdate]<=5),

    'Table 1'[value])

after that i used this measure to calculate the average on the last 5 dates.

If this helped you accept it as solution.
Connect on: Linkedin 

[JoeBarry]

Hi @rmeng 

Please refer to this article from @AlbertoFerrari , it will help a lot https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/ 

Thanks

Joe

If this post helps, then please Accept it as the solution