 # 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

***** Related Course Modules *****
Time Intelligence Calculations
Solving Analytical Scenarios W/ Power BI & DAX

## 17 comments on “Calculate A Year-To-Date (YTD) Monthly Moving Average In Power BI”

1. Wuzizname says:

This doesn’t work. What is “MonthOfYear” and how is it different for “Month & Year” in the Dates table?

“Calculation error in measure ‘Shipments'[AVG_YTDMonthly]: A function ‘FILTER’ has been used in a True/False expression that is used as a table filter expression. This is not allowed.”

I’d never expected calculating a moving monthly average in excel would be so bloody hard but this is like the 5th article I’ve tried and none of them work.

1. Sam McKay, CFA says:

Hello Wuzizname,

Thank You for posting your query onto our blog post.

Well “MonthOfYear” is the Month Number of a particular month i.e. for January = 1 and so on.

When using the time intelligence functions mostly the fields that contains numbers or dates will provide the results. So in this case, “Month & Year” is in the text format and therefore text format cannot be used like this – Dates[Month & Year] <= MAX( Dates[Month & Year] ). Also without looking at the data structure and formula in your PBIX file, it's not possible for us to judge and provide the results efficiently and therefore we encourage you to please write back to us onto our Community Forum by providing the description of the problem that you're facing along with the working of the PBIX file and the mock-up of the solution that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful. Happy Learning!!! https://forum.enterprisedna.co/

2. Wuzizname says:

My bad, missed a closing bracket on the FILTER function. Please, please write more posts about calculations over time frames using DAX it’s stewing my brain trying to get the hang of it.

Thank you!!

1. Sam McKay, CFA says:

Hi Wuzizname, appreciate the suggestion. We will put this on our list.

3. Morten Bertelsen says:

Hi Sam. Thank you for a very useful tutorial. How would you convert the formulas if I was to use on a daily basis instead of monthly, and MTD instead of YTD? I have tried changes the formulas, but without luck.

The thing is; I have a dataset with index prices, so I will need to calculate daily averages instead of sums, since I am interested in the daily moving average index price.

4. Matt says:

Hi Sam,

How would you apply the same logic for a fiscal year? To calculate Revenue FYTD I would go:

Revenue FYTD =
TOTALYTD(
[Total Revenue],
‘Calendar'[Date],”30-Jun”
)

But…how would you would incorporate an average into this?

Thanks
Matt

1. Sam McKay, CFA says:

Hi Matt – Thanks for your interest in Enterprise DNA Blogs.

For non-standard calendar, Need to add Financial year/month details in Calendar tables similar to standard Calendar .

In Calculations, need to make use of DAX functions like Filter and ALL to get the required dates.

Refer below Blog posts for more information.

5. Eugene Munley says:

Hey Sam, is there a way to ignore zeros? I am working with survey results opposed to a value like revenue and I need my YTD monthly average calc to ignore month/year combos where no surveys were received. Thanks!!

1. Sam McKay, CFA says:

Hi Eugene,

Thanks for your interest in Enterprise DNA Blogs.

For your requirement, can add an additional condition in YTD Monthly Average to exclude Dates with value = 0 like below. This will exclude all Dates where Total sales was 0.

YTD Monthly Average = CALCULATE([Monthly Avg],filter(all(Dates),Dates[Date] <= max(Dates[Date]) && Dates[Year] = max(Dates[Year]) && [Total Sales] <> 0))

If issue is still not resolved, then raise a request at EDNA Forum https://forum.enterprisedna.co/ and our team of experts will help you.

This site uses Akismet to reduce spam. Learn how your comment data is processed.