# Calculate A Year-To-Date (YTD) Monthly Moving Average In Power BI

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.

## 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

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## Cumulative Totals Based On Monthly Average Results In Power BI

Today, I wanted to cover a unique technique around cumulative totals based on monthly average results...

## Power Automate Expressions – An Introduction

In this tutorial, weâ€™ll explore Power Automate expressions and how to use them in workflow automation....

## List.Max Power Query: User Guide With Examples

One of the most powerful and useful functions in Power Query M language is List.Max. It can be...

## R Scripting For Power BI Using RStudio

In this blog, weâ€™re going to go through the basics of R scripting for Power BI using RStudio. The...

## Simple Aggregations In Power BI

In this blog post, I'll touch on some simple aggregations in Power BI that you can use in your DAX...

## Microsoft Report Builder: Steps In Creating A List

In this tutorial, you will learn how to create a list and page break in Microsoft Report Builder. A...

## 5 Strategies To Enhance Your Power BI DAX Skills

In this tutorial, youâ€™ll learn the general strategies to improve your DAX or M capabilities. This is an...

## ALL Function in Power BI – How To Use It With DAX

Did you know that the ALL function can be used to modify the context of a particular calculation in...

## Calculations In Power BI Using Measure Branching

Measure Branching is a technique in making calculations in Power BI. It's not something you'll hear...

## Power BI Ranking In Hierarchical Form

Today, we will learn how to calculate Power BI ranking in a hierarchical form which is a little bit...

## Understanding Power BI Aggregations

Now, let's delve deeper into one of the most important concepts in Power BI calculations â€” the...

## Using Python In Power BI | Dataset And String Function

In this tutorial, we'll discuss how you can create and prepare Dataset and String Function using Python...