This is a deep dive tutorial on how to calculate the Year-To-Date Cumulative and Monthly Moving Average in Power BI. You may watch the full video of this tutorial at the bottom of this blog.

The example used in this tutorial is from a question in the Enterprise DNA Support Forum.

Calculation of Months

Aside from working out the Moving Average, we also wanted to show the results in a table with a Year and Month context.

So, for this ytd Power BI example, you’ll be learning how to work out the Year-To-Date Moving Average.

Calculating The Monthly Moving Average

The solution to this problem will depend on the context you want your information to be in.

For this example, the context of the table is Year and Month:

This is the formula for Total Revenue:

It’s a simple aggregation formula that uses the SUM function in Power BI.

The next formula is for the Year-To-Date Revenue. It uses the simple time intelligence function TOTALYTD:

All you need to do for this formula is input the Total Revenue measure you’re branching out, and the Dates Column.

Then, you need to create another formula called Monthly Average:

This formula is calculating the average for each different month. You need to put a virtual table inside the iterating function AVERAGEX.

This iterates through every single Month and Year in the virtual table. It will calculate the Total Revenue and then find the average.

Monthly Average Vs YTD Monthly Average

In the table, you’ll notice that the Total Revenue has the same amount as the Monthly Average.

This occurs because the virtual table inside AVERAGEX is only working out one month for every single row. So, it doesn’t iterate through the months where you want to calculate the average.

You’re getting the same results because you’re only getting the average for one month.

But under the Year-To-Date (ytd) Monthly Average column, you’ll see the changes in the amounts as it goes through time.

This happens because the formula is averaged based on the Cumulative Total or Year-To-Date Column.

Every row looks through the months prior to and including the current month. It then calculates the moving average of the revenue associated with those months.

The Year-To-Date Monthly Average Formula

The next formula you need is the Year-To-Date Monthly Average formula.

It’s a different formula compared to other averaging formulas because of how it can filter through the context in a table.

In this case, you want to calculate the YTD Monthly Moving Average but in a different context from the other columns in the table.

For example, in the third row of the table, excluding the headings, its context for the first two columns is 2018 March.

However, you want the context of the Year-To-Date Monthly Average to also include the revenue of the months before it. So, instead of just March 2018, you want it to be from January, February, and March of the same year.

The FILTER statement inside the YTD Monthly Average Formula allows you to remove the context from Dates for every row and then reapply it.

If the Month of Year is less than or equal to the next Month of Year, it will calculate the monthly average of those months.

Here’s an example:

Only 2018 is selected in the slicer.

So, for the third month, it will calculate the average of months 1, 2, and 3. That same logic will apply to all the rows in the table.

***** Related Links *****
Calculate Financial Year To Date (FYTD) Sales In Power BI Using DAX
Preventing Year To Date Results From Projecting Forward
Using Moving Averages To Show Trends In Power BI

Conclusion

This tutorial went over how to calculate the Year-To-Date Monthly Moving Average in Power BI. What makes this formula unique is how it’s able to get different contexts from the same table using the FILTER function.

By starting with a simple measure and then slowly branching it out, you’re able to understand how the data is used to get the desired result.

This technique can also be used in other business-related scenarios. It can be used to calculate your costs or quantity sold, among other things.

You can check out the entire forum discussion for this example by clicking on the related support forum links below.

All the best,

Sam