Power Query - How do you create a Rolling 12 month average?

Question

Power Query - How do you create a Rolling 12 month average?

PQ 20

How do you create a rolling 12 month average in Power Query? For example, I am taking the last 12 months of data and creating a formula to get the average (i.e. the sales revenue x # of days in the month). See attached screenshot for details.

Every month I have to grab the most recent last 12 months of data, so I'm updating all my formulas manually across the 12 month period. How do I automate this in M language in Power query so it just automatically rolls and gives me the data in a new column?

PQ 20 Reputation points

2025-10-15T04:18:13.6766667+00:00

Screenshot - Rolling 12 months.jpg

Answer accepted by question author

Kai-Ex 1,480 Microsoft External Staff Moderator

Dear @PQ,

Thank you for posting your question in the Microsoft Q&A forum.

Based on my environment test and my understanding, please follow the steps below to prepare and transform your data effectively: Move your source data into the designated location or file where you will perform the transformation. Load the data into Power Query by selecting Data > Get Data > From Table/Range Your dataset may contain preliminary rows before the actual header row. Ensure that the row containing month names and key indicators (Sept 2024 Av, 30, 109,000.00) is promoted as the header.

Home > Use First Row as Headers. If necessary, use Remove Rows to keep only the three essential rows (Month/Indicator, Days, Revenue) before applying Transpose.

In Power Query Editor, go to Add Column > Custom Column and enter the following formula:

= Table.AddColumn( 
    #"Changed Type1", 
    "Rolling 12 Month Avg", 
    (CurrentRow) => 
        let 
            // 1. Calculate the 'Monthly Value' for the current table 
            MonthlyValuesTable = Table.AddColumn( 
                #"Changed Type1", 
                "Monthly Value", 
                each [#"109000"] * [#"30"], 
                type number 
            ), 
            // 2. Filter the table to include the 12 months before or including the current row's date 
            CurrentDate = CurrentRow[Sept 2024], 
            FilterDate = Date.AddMonths(CurrentDate, -11), 
            // 3. Select the 'Monthly Value' column from the filtered rows 
            RollingValues = Table.SelectRows( 
                MonthlyValuesTable, 
                each [Sept 2024] >= FilterDate and [Sept 2024] <= CurrentDate 
            )[Monthly Value] 
        in 
            // 4. Calculate the average of the rolling values 
            List.Average(RollingValues) 
)

User's image

Note: Adjust column references in the formula to match your actual dataset structure.

If there’s anything I may have misunderstood, I would truly appreciate it if you could provide a bit more detail to help me better understand your needs. Also, if you’re stuck on anything or find something unclear, please feel free to reply so I can guide you further.

Looking forward to your response! If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Jeanie H 11,915 Reputation points Microsoft External Staff Moderator

2025-10-15T23:21:46.71+00:00

Dear @PQ,

I hope you’re doing well. I just wanted to check if my previous response addressed your question correctly. If there’s anything I may have misunderstood, I would truly appreciate it if you could provide a bit more detail to help me better understand your needs.

Also, if you’re stuck on anything or find something unclear, please feel free to reply so I can guide you further.

There’s absolutely no rush - please respond whenever it’s convenient for you. I’m here and ready to support you whenever you need assistance. Your comfort and timing are important, so take all the time you need.

Thank you for your time, and I look forward to hearing from you whenever you’re ready.
PQ 20 Reputation points

2025-10-15T23:28:38.3433333+00:00

Thank you! I'm building it now and then I need to test it to see if it works. I'll get back to you shortly.
Jeanie H 11,915 Reputation points Microsoft External Staff Moderator

2025-10-16T00:45:31.7266667+00:00

Dear @PQ,

Take your time testing it and let me know how it goes. I’ll be here if you need any help or run into any issues.
PQ 20 Reputation points

2025-10-16T01:28:31.4733333+00:00

Unfortunately it didn't work. It doesn't appear you fully understand what I'm trying to do.
PQ 20 Reputation points

2025-10-16T03:06:19.4466667+00:00

For now, I'm going to keep my formula in excel. The switch to power query doesn't appear to be working properly. Thanks anyway!
Jeanie H 11,915 Reputation points Microsoft External Staff Moderator

2025-10-16T03:45:23.3066667+00:00

Dear @PQ,

I’ve reviewed input from other users and found that performing this calculation using standard Excel functions is simpler and more intuitive. These functions are designed to handle calculations directly within the worksheet without extra steps, making it easier to maintain and follow.

On the other hand, Power Query is typically better suited for tasks like: Cleaning data (removing spaces, standardizing formats, eliminating duplicates), combining multiple data sources or worksheets, transforming data structure before analysis.

So, using Excel formulas will save time and reduce complexity.

You might consider accepting this answer because this helps other users facing similar situations. When an answer is confirmed, they can feel confident and save time searching, creating a stronger, more helpful community.

If you have any further questions, feel free to ask. Thank you for contributing and supporting the community!

2 additional answers

Your answer

PQ 20 Reputation points

2025-10-15T04:18:13.6766667+00:00

Screenshot - Rolling 12 months.jpg
Jeanie H 11,915 Reputation points Microsoft External Staff Moderator

2025-10-15T23:21:46.71+00:00

Dear @PQ,

I hope you’re doing well. I just wanted to check if my previous response addressed your question correctly. If there’s anything I may have misunderstood, I would truly appreciate it if you could provide a bit more detail to help me better understand your needs.

Also, if you’re stuck on anything or find something unclear, please feel free to reply so I can guide you further.

There’s absolutely no rush - please respond whenever it’s convenient for you. I’m here and ready to support you whenever you need assistance. Your comfort and timing are important, so take all the time you need.

Thank you for your time, and I look forward to hearing from you whenever you’re ready.
PQ 20 Reputation points

2025-10-15T23:28:38.3433333+00:00

Thank you! I'm building it now and then I need to test it to see if it works. I'll get back to you shortly.
Jeanie H 11,915 Reputation points Microsoft External Staff Moderator

2025-10-16T00:45:31.7266667+00:00

Dear @PQ,

Take your time testing it and let me know how it goes. I’ll be here if you need any help or run into any issues.
PQ 20 Reputation points

2025-10-16T01:28:31.4733333+00:00

Unfortunately it didn't work. It doesn't appear you fully understand what I'm trying to do.
PQ 20 Reputation points

2025-10-16T03:06:19.4466667+00:00

For now, I'm going to keep my formula in excel. The switch to power query doesn't appear to be working properly. Thanks anyway!
Jeanie H 11,915 Reputation points Microsoft External Staff Moderator

2025-10-16T03:45:23.3066667+00:00

Dear @PQ,

I’ve reviewed input from other users and found that performing this calculation using standard Excel functions is simpler and more intuitive. These functions are designed to handle calculations directly within the worksheet without extra steps, making it easier to maintain and follow.

On the other hand, Power Query is typically better suited for tasks like: Cleaning data (removing spaces, standardizing formats, eliminating duplicates), combining multiple data sources or worksheets, transforming data structure before analysis.

So, using Excel formulas will save time and reduce complexity.

You might consider accepting this answer because this helps other users facing similar situations. When an answer is confirmed, they can feel confident and save time searching, creating a stronger, more helpful community.

If you have any further questions, feel free to ask. Thank you for contributing and supporting the community!

Answer 1

IlirU 2,491 Volunteer Moderator

User's image

Hi,

Try below formula in cell M4:

=LET(d, A3:L3, SUM(MAP(d, A4:L4, PRODUCT)) / SUM(d))

Hope this helps.

0 comments

Answer 2

Ashish Mathur 101.8K Volunteer Moderator

Hi,

With the way your data is arranged, writing an Excel formula would be much simpler. In cell H4, enter this formula

=LET(a,L3:XLOOKUP(EDATE(M2,-11),A2:L2,A3:L3),SUM((L4:XLOOKUP(EDATE(M2,-11),A2:L2,A4:L4))*(a))/SUM(a))

The headings in row 2 are actuals date such as 01/09/2024, 01/10/2024 etc. They are formatted as mmm-yy.

Hope this helps.

User's image

0 comments

Share via

Power Query - How do you create a Rolling 12 month average?

2 additional answers

Your answer