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.
First, some math
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:
/ = 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: / = 547.23 / 9 = 60.8 Or / = 547.23 / 10 = 54.72 The second way of doing it is only a different definition. For example, suppose the first column is a Customer ID, and I want to calculate the Average Sales for all Customers or the number of active Customers, etc. In that case, the second way of calculating the Average might be the better approach. Now, let’s translate this into Dax. ## Moving Aggregation – The starting point First, let’s build the typical case of Moving Aggregation. I want to get the Moving Sales over the last four months. For now, I use the [SUM()](https://dax.guide/sum/) of Sales as it’s much easier to validate the result over four months than by calculating the average. “` Sales Moving Sum = VAR MaxDate = MAX( ‘Date'[Date] ) VAR MinDate = CALCULATE( MIN(‘Date'[Date]) , DATEADD( ‘Date'[Date], – 3, MONTH ) ) VAR DateRange = CALCULATETABLE( DATESBETWEEN( ‘Date'[Date] ,MinDate ,MaxDate ) ) VAR Result = CALCULATE([Sum Online Sales] ,DateRange ) RETURN Result “` First, I get the last Date for the Current Filter Context (e. g. the current month) Second, I use [DATEADD()](https://dax.guide/dateadd/) to move back three Months. I only move back for three months because I include the current month. If I want to exclude the current month, I must do it differently. In this case, I must get the first date and go back one day to get the last date of the previous month (Or use [EOMONTH(MAX(‘Date'[Date), -1) )](https://dax.guide/eomonth/). Then, use DATEADD() to go back four months). Third, I use [DATESBETWEEN()](https://dax.guide/datesbetween/) to get a list of dates between the two Variables. Lastly, I pass the list of dates on to [CALCULATE()](https://dax.guide/calculate/) to return the final result. Here is the result:  I can simplify the Measure by removing the DATESBETWEEN() function and passing the two Variable directly to CALCULATE(): “` VAR MaxDate = MAX( ‘Date'[Date] ) VAR MinDate = CALCULATE( MIN(‘Date'[Date]) , DATEADD( ‘Date'[Date], – 1, MONTH ) ) VAR Result = CALCULATE([Sum Online Sales] ,’Date'[Date] >= MinDate && ‘Date'[Date] [**How to get performance data from Power Bi with DAX Studio**](https://towardsdatascience.com/how-to-get-performance-data-from-power-bi-with-dax-studio-b7f11b9dd9f9) ## Let’s do Average Now, finally, I will start calculating the average. I use the same logic as in my Sum Online Sales Measure and use AVERAGEX() to calculate the average Sales Amount: “` Average Online Sales = AVERAGEX(‘Online Sales’ ,(‘Online Sales'[UnitPrice] * ‘Online Sales'[SalesQuantity]) – ‘Online Sales'[DiscountAmount] ) “` Next, I copy the Measure above to calculate the Sales Moving Average, and this is the Result:  I could end here and write, “Mission accomplished.” But stop. At the start, I mentioned different ways to calculate an average. So, I started writing Measures to test them out. I wrote the following Measures, which I used as a denominator, while I used the [Sum Online Sales] and the Nominator: – Count Online Sales = COUNTROWS(‘Online Sales’) – Customer Count = DISTINCTCOUNT(‘Online Sales'[CustomerKey]) – Online Order Count = DISTINCTCOUNT(‘Online Sales'[SalesOrderNumber]) The code for the Result Variable is the following (Using [Count Online Sales] as an example): “` VAR Result = CALCULATE([Sum Online Sales] / [Count Online Sales] ,DateRange ) “` I can think of more variants (e.g., average overall customers, even those without an order during that period). But I decided to stop here so as not to avoid confusion. Unsurprisingly, each of them delivered a different result:  The difference between the results is very drastic. You might encounter other ways to calculate the Average. Therefore, I urge you to define clearly how the average must be calculated. Otherwise, you might deliver unexpected or even incorrect results to your Audience. ## Average over Monthly Sales There is one more variance in calculating the Average: As an Average over Monthly Sales. Let’s look again at the result with the monthly Sales:  I want to calculate the average monthly sales over four months. For example, in September, I want to calculate the Average of the monthly sales in June, July, August, and September: (275’061’552.33 + 303’302’950.82 + 273’004’268.56 + 262’971’889.59) / 4 = ~278’585’165.3 (~ because we might get a slightly different result because of rounding differences) To fulfill this requirement, I must think about how to do it. I need to calculate the Sales Amount for each month in advance. Then consider only the four months required for each row. At the end, calculate the Average for these four values. This would mean I must first generate a table with all monthly results and use only the value needed to calculate the Average, which is inefficient. Power BI would then calculate this for each row in the table to visualize the result. When I look at it from the perspective of the Filter Context per row, I can do it much better. Why not calculate the sum of sales only for those months relevant to each row in the visualization? Based on this approach, I wrote the following Measure: “` Moving Average by Month = // 1. Get the first and last Date for the current Filter Context VAR MaxDate = MAX( ‘Date'[Date] ) VAR MinDate = CALCULATE( MIN(‘Date'[Date]) , DATEADD( ‘Date'[Date], – 3, MONTH ) ) // 2. Generate the Date range needed for the Moving average (Four months) VAR DateRange = CALCULATETABLE( DATESBETWEEN( ‘Date'[Date] ,MinDate ,MaxDate ) ) // 3. Generate a table filtered by the Date Range generated at step 2 // This table contains only four rows VAR SalesByMonth = CALCULATETABLE( SUMMARIZECOLUMNS( ‘Date'[MonthKey] , “#Sales”, [Sum Online Sales] ) ,DateRange ) RETURN // 4. Calculate the Average over the four values in the table generated in Step 3 AVERAGEX(SalesByMonth, [#Sales]) “` This time, I added inline comments to explain what was happening there. The result is the following:  I checked the results in Excel, and they are correct. If you’re thinking about creating a pre-calculated table per month, think about it again. You would be forced to add references to all Dimensions and increase the granularity of the data to the point that you need to write this Measure anyway to fulfill your audience’s need to filter the data by all Dimensions. This solution is very efficient, as it took less than 0.4 seconds to calculate the result. Even when expanding all Months, the results didn’t need more time to calculate. By the way, this approach is also suitable when calculating an average of averages. ## Conclusion The Average is not equal to the Average. I think this is clear. But more than this, it’s essential to understand what should be calculated and how. Understanding why a number must be calculated can help you choose the correct calculation logic. When the logic is clear, the Approach to writing the DAX code should be defined. Remember to do it from the perspective of the Filter Context. Sometimes, this is counterintuitive, but it will help develop efficient code. I hope that you learned something new today. See you next time.  on [Unsplash](https://unsplash.com?utm_source=medium&utm_medium=referral)](https://miro.medium.com/0*sRHUWHr6PjbuSbQ6) ## References Like in my previous articles, I use the Contoso sample dataset. You can download the ContosoRetailDW Dataset for free from Microsoft [here](https://www.microsoft.com/en-us/download/details.aspx?id=18279). The Contoso Data can be freely used under the MIT License, as described [here](https://github.com/microsoft/Power-BI-Embedded-Contoso-Sales-Demo). I changed the dataset to shift the data to contemporary dates. > [**Get an email whenever Salvatore Cagliari publishes.**](https://medium.com/@salvatorecagliari/subscribe) I make my articles accessible to everyone, even though Medium has a paywall. This allows me to earn a little for each reader, but I turn it off so you can read my pieces without cost. I write these pieces in the evenings and at the weekends, which is a lot of work. You can support my work through [https://buymeacoffee.com/salvatorecagliari](https://buymeacoffee.com/salvatorecagliari) Or scan this QR Code:  Any support is greatly appreciated and helps me find more time to create more content for you. Thank you a lot.
