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

10 comments

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.

calculate moving average

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 tutorial, 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:

calculate moving average

This is the formula for Total Revenue:

calculate moving average

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:

calculate moving average

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:

calculate moving 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 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.

calculate moving average

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.

calculate moving average

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

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resource
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events

membership banner 3

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

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

***** Related Support Forum Posts *****
Cumulative Year To Date & Monthly Average Year To Date
Showcase Cumulative YTD And Average Month YTD
Removing Context From SUMMARIZE

For more YTD monthly average support queries to review see here….

This image has an empty alt attribute; its file name is enterprise-dna-events-1-1.png

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

  1. 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. 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. 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!!

  3. 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. 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. 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.

      Date Table -: https://blog.enterprisedna.co/how-to-create-a-detailed-date-table-in-power-bi-fast/
      Calculations -: https://blog.enterprisedna.co/compare-time-periods-with-non-standard-date-tables-dax-and-power-bi/

Leave a Reply

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