Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
rmeng
Helper II
Helper II

moving average of the last 5 days with values

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 

rmeng_0-1692824479185.png











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## FirstDayInPeriodAAAA## Moving average 5 days
1/4/202212/31/202144.98 
1/5/20221/1/20228.96 
1/6/20221/2/202261.05 
1/7/20221/3/20229.77 
1/10/20221/6/202212.6227.813932


Could you help me changing the measure?

THANKS




1 ACCEPTED SOLUTION
SiemdeKort
Frequent Visitor

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 

View solution in original post

3 REPLIES 3
SiemdeKort
Frequent Visitor

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
Frequent Visitor

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
Solution Sage
Solution Sage

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 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors