cancel
Showing results for
Did you mean:

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

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

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

1 ACCEPTED SOLUTION
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.
3 REPLIES 3
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
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.
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