Calculating a moving Aggregation is easy in DAX. However, there are some pitfalls when calculating the moving average over time. As some of these pitfalls are a question of definition, we must be careful not to choose the wrong method. Let’s look at the details.
Calculating the Average is easy: Divide the sum of a value by the number of instances.
While the sum of a value is easy, the number of instances is not as straightforward as you might think.
For example, let’s look at the following table:
The calculation of the Average of the Value column is easy:
<Sum of Value> / <No of Rows> = 534.68 / 10 = 53.47
Now, let’s delete one value, and this changes the picture.
Suddenly, I have two ways of calculating the Average:
<Sum of Value> / <No of Values> = 547.23 / 9 = 60.8
Or
<Sum of Value> / <No of Rows> = 547.23 / 10 = 54.72