skip to Main Content

power pivot to power bi

A Power BI Technique Mined from the Power Pivot Archives

Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI.  Hard to imagine - even for us - but true.

Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.” 

Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables.  Enjoy! Smile

Moving Average in Power Pivot

The Blue Line Smooths Out Random Fluctuations, Tells a Less “Over-Reactive” Trend

I realized recently that this topic has never been covered before, in its most straightforward form, on this site!  Actually, it was the subject of a guest post by the esteemed David Churchward, and also by the equally-esteemed Kasper de Jonge, but neither of those posts benefited from the v2 functions available to us today).

To illustrate what we can do with state-of-the-art Power Pivot formulas, let’s start with this simple model:


Simple Power Pivot Model

And a simple pivot:


That [Units Sold] measure is the jagged red line in the chart at the top of the post, and its formula is very simple:

[Units Sold] = SUM(Sales[QtySold])

And we want a version of [Units Sold] that is “smoothed” over a 3-month period.

Moving Sum

Let’s start with a formula that is a sum of the most recent 3 months (including the current one):

[3 Month Moving Sum Units Sold] =
CALCULATE([Units Sold],
                        LASTDATE(Calendar[Date]),-3, Month

And see what that looks like:

Power Pivot Moving Sum

Moving 3-Month Sum Reflects the Current Month and the Prior Two Months

Moving Average – First Attempt

OK, but that number is bigger than a single month and doesn’t match the scale of our real-world business, so we wouldn’t want to chart that – we want the average version of that.

It’s a 3-month moving sum, so to get the average, we could just divide by 3:

[3 Month Avg Divide 3] =
=[3 Month Moving Sum Units Sold] / 3

Which looks like:

Power Pivot Moving Average with a Drawback

3 Month Moving Avg Via Divide by 3 Has a Drawback

Those first two months, since they are the first two months in our calendar, are summing up less than 3 months’ worth of sales, but still dividing by 3.  So it “unfairly” drives down their average.

Moving Average – Corrected

We can account for this by changing our denominator to use a similar logic to the numerator:

[3 Month Moving Avg Corrected]=

[3 Month Moving Sum Units Sold] /

In English:  “take the 3 month sum measure we already have and divide it by the number of distinct (unique) months we have over that same 3 month period.”


Power Pivot Moving Average Fixed

This Calc is More “Fair” to the Months at the Beginning


There are a number of variations on this approach – daily/weekly/quarterly versions, correcting for calendars that extend beyond the range of dates where you have sales, adapting it to custom calendars via the Greatest Formula in the World, etc., but I will wait and see what people ask about in the comments before digging into any of those.

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 31 Comments
  1. Is it complicated to control “X Months moving” from Excel?
    Have a cell in Excel where we can put 3, 6, 12 or even 1.

      1. Having a slicer to easily/quickly (re)set the moving average length would GREATLY enhance the ability to see the effect of the ma-length on the graph / analysis…

        1. Also, I have created columns in the PP data model having differing moving-average lengths (c.f., lagged: 1,3, 6 mos; & fwd/backward: 1, 3, 6 mos) for a 1.8M row revenues-ledger table. The 2013 .xlsb file has ballooned from ~220MB to over 400MB. Being able to ‘dynamically’ change the ma-period should COMPRESS ALL of this to just a FEW columns — (as well as make it MUCH easier to investigate / select the effects of the ma-length)…

      2. to may it control ‘X months moving’ , how do i use slicer to replace the number of intervals(-3)? Please advise. Thanks a lot

        1. Just use the Search box at the top of the page Billy. “Moving Average Slicer” should do the trick 🙂

  2. I had a different problem: what to do if don’t know exact length of the period? Imagine the situation of some technology where you need to keep things in certain conditions over some period of time and it varies. Simply you have two dates START and END. In Excel it is not difficult using SUMPRODUCT or some kind of array stuff but it’s slow and COPY/PASTE routine is required each time you update. How to do it in DAX then when you keep process parameters in separate table? First thing, setting a relationship led me nowhere (in my opinion it is variation of MANY to MANY when you have parallel processes). I tried to apply USERELATIONSHIP but failed. The solution I found is simple but you need cumulative data. The formula of SUM over the period from A to B looks like: value(B)-value(A), AVERAGE: (value(B)-value(A))/(B-A). The funniest part is you don’t need any relationship at all, just take LOOKUPVALUE to find value(B) and value (A). Maybe this solution is obvious to everyone but for me was not but I found it refreshing.
    Greetings from Poland.
    Forgive me my English – I do not use it very often

  3. Hi I Used the Fomula Calculated Field 1:=Calculate([Sellout Product Units],Datesinperiod([Date],lastdate([Date]),-3,Month)).. the Date feild in e.g.- 5/1/2013 12:00:00 AM, i got an error stating DatesinPeriod Function are only accepting date column reference as a first argument

  4. Hi!

    I would love some help to do the “correcting for calendars that extend beyond the range of dates where you have sales”, as you write in the variations above.

  5. I am struggling to do this for 7 day moving average:


    Could anyone advice me on this please.

    Kind Regards.


  6. Hi, I’ve tried to replicate the 3-month moving average in my file. I followed the instructions above. My moving sum/average only contains the last 3 months (not the whole period with their corresponding data as showed in the first picture above, i.e.I got date only for 2001/09, 2001/10, 2001/11). Could anyone advice what could be the problem

  7. It works! But…..I am working with days – specifically, I need to get a rolling 28 day average of one of my measures. This works fine when I view the result with my date attribute. However, my Calendar dimension also has a discreet, non-continuous attribute called DateMonth (which looks like 1-Jan, 2-Jan etc.)

    I was hoping this would allow me then to take my year attribute and compare the 28 Day Average for the 1st of Jan across multiple years.

    So you could have a line chart where the axis is 1-Jan to 31-Dec (with no reference to the year) and the series category is by year, so a line for each year.

    Hope someone could help 🙂

  8. The last equation provided – Moving Average Corrected – delivers a denominator with a value of 12 (i.e., it is dependent on when my Calendar[year-month] starts. So to have a correct 12 month moving average, my data and calendar need to start at the same time). Any suggestions?

    1. I figured it out:


      What I did was started counting my months starting from the first date of my sales (i.e., 40663 is the last date before I wanted to start counting the months where my sales were).

      Hope this can help someone searching this blog post!

      1. William, you are a life saver. I was attempting to figure out why my denominator held 3 for the oldest month of data I was working with. I appreciate your example and explanation which applied exactly to my situation.

  9. I have a better solution to the final comment in here – one that calculates the number of months to divide by, if your data and calendar do not start at the same time.

    This was the original calc:

    [3 Month Moving Avg Corrected]=

    [3 Month Moving Sum Units Sold] /
    CALCULATE(DISTINCTCOUNT(Calendar[Year Month]),

    And its within the divider that the edit is needed – to correctly work out where to divide by 2 and then 1 (for the first 3 months in your data).

    The below works a treat:

    3 Month Moving Average – Working = [3 Month Moving Sum]/
    CALCULATE ( COUNTROWS ( VALUES ( DimDate[CalendarMonth] ) ), FactSales ),

    Where FactSales is your fact table obvs.

    hope this helps someone else out – as it drove me mad!


    1. This works great, however i adapted it to a 7 day moving average, however the last 7 days are still divivded by 7, making a large drop off, any idea how to fix that?

  10. Hello Experts,

    I have a question.

    What if we need the average of last 30 Trading days or working days.??

    Of Course, we would have a Date column representing trading days.and corresponding sales amount.

    1 Month average with your formula won’t be the same thing. As Exact 30 days’s average is needed here.

    Would you please suggest a formula ??


  11. The difficult thing is the calculation is based on the financial date other than the calendar date, as almost all existing time intelligent date functions cannot be used. Any sample calculation formula based on the financial date?


    1. I got a plenty of info about the calculations based on the customer dates from the above link “Greatest Formula in the world” . It’s very helpful…..

  12. This is a great posting by all. I want to know what if the sales in one of the past three months is zero or no sales at all, then how would the formulas suggested work.
    I want to find out the number of months from current month to last three months having sales. Say I am in May 2014 the last three months will be including May 2014 , Mar 2014, Apr 2014 and May 2014. But Apr 2014 does not have sales. So it should be sum of Mar and May 2014 sales divided by 2 and not 3.
    Assume we have sales for Jun 2014 and Jul 2014.
    For Jun 2014 the average last 3 months should be ( My 2014 + Jun 2014) / 2.
    For Jul 2014 the average last 3 months should be ( May 2014 + Jun 2014 + Jul 2014 ) / 3.

    Any help to achieve this will be greatly appreciated.


    1. Hey there, brilliant solution.

      But now I’m looking for the same addition as CheenuSing mentioned.

      Some series in my data don’t have sales in the last period but others do. CheenuSing, have you found the solution to that? I’d be most grateful, thanks.

  13. Hi Everyone!

    I´m new with all DAX functions and quite struggling with my moving average calculation. I just need the moving average of 3 months back NOT including the current month. I tried using “IF” but never worked out. Can some one help me with it?

  14. So this is great except I have 3 months of the moving average showing into the future that I want to not display on my chart. Since I can’t add a visual filter for date <= today, how do I integrate that into the queries?

  15. when I am trying to replicate this formula, it is only calculating last 3 months of the year correctly for moving average but not for the rest…

  16. I have a different situation
    I have a fact table with Employee Qty for each month and need to calculate Average employee Quantity for each month

    Total employee at the end of the month Average (what I need to obtain with my calculations: (Qty actual moth+ Qty prior month) /2 )
    May 258 258
    June 256 257
    July 255 256
    Aug 215 235
    Sep 215 215
    Oct 202 209
    Nov 201 202
    Dec 201 201
    Jan 198 200
    Feb 202 200
    Mar 208 205
    April 212 210

    I am struggling with this. Anybody can help me?

  17. Hi,
    thank you very much for info.
    Please give me advice: I want see figures for current date and for the same date year ago if I select in slicer one date (month+year).
    Is it possible to make in one measure?
    For example, I select aug.2017 in slicer and
    on my diagram I see data for aug.2017 – 3 months moving sum and for aug.2016 – 3 months moving sum

    thank you in advance

Leave a Comment or Question