Forum Discussion

MikePowerBI's avatar
MikePowerBI
Helper II
4 years ago
Solved

Three Month Moving Average Column

Hi there,

 

I am struggling with creating a column which calculates a 3 month moving average similar to the example below. The 3 month moving average would be the same number for 3 months and based upon the 3 months prior.  

 

DateUnits3 Month Moving Average
1/1/202025 
2/1/202030 
3/1/202015 
4/1/20205023.33333333
5/1/20206023.33333333
6/1/20208823.33333333
7/1/20206666
8/1/20205066
9/1/20202066
10/1/20204545.33333333
11/1/20206545.33333333
12/1/20207045.33333333

 

Any help with this would be greatly appreciated!

Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    4 years ago

    Hi MikePowerBI ,

     

    Please create a QTR column then create a measure like below:

    Measure = 
VAR CURRENT_QTR =SELECTEDVALUE('Table'[QTR])
var sum_ = 
SUM('Table'[Units])
return CALCULATE(AVERAGE('Table'[Units]),FILTER(ALL('Table'),'Table'[QTR]=CURRENT_QTR-1))

     

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    4 years ago

    Hi MikePowerBI ,

     

    Please create a QTR column then create a measure like below:

    Measure = 
VAR CURRENT_QTR =SELECTEDVALUE('Table'[QTR])
var sum_ = 
SUM('Table'[Units])
return CALCULATE(AVERAGE('Table'[Units]),FILTER(ALL('Table'),'Table'[QTR]=CURRENT_QTR-1))

     

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • serpiva64's avatar
    serpiva64
    Solution Sage
    4 years ago

    Hi,

    Try this one

    3 Month Running Total =
    Var SelectedMaxDate = MAX ( Dates[Date] )
    Var MinDate =
    CALCULATE (
    MIN ( Dates[Date] ),
    FILTER (
    ALL ( Dates ),
    DATEADD (
    Dates[Date],
    3,month
    ) >= SelectedMaxDate
    )
    )
    Return
    CALCULATE (
    SUM ( 'Table'[Start of Year Headcount] ),
    ALL ( Dates ),
    Dates[Date] <= SelectedMaxDate,
    Dates[Date] >= MinDate
    )
     

    If this post is useful to help you to solve your issue consider giving the post a thumbs up 

     and accepting it as a solution !

    • MikePowerBI's avatar
      MikePowerBI
      Helper II
      4 years ago

      Hi serpiva64, thank you for the prompt reply!

       

      Within the formula, could you explain what the [Start of Year Headcount] refers to please?

       

      I used the Units column in the example above and this formula output a single number into the entire column. 

      • serpiva64's avatar
        serpiva64
        Solution Sage
        4 years ago

        sorry, 

        i misunderstood your question.

        This is the formula of with your data

        3 Month Average Running Total =
        Var SelectedMaxDate = MAX ( Dates[Date] )
        Var MinDate =
        CALCULATE (
        MIN ( Dates[Date] ),
        FILTER (
        ALL ( Dates ),
        DATEADD (
        Dates[Date],
        3,
        MONTH
        ) >= SelectedMaxDate
        )
        )
        Return
        CALCULATE (
        SUM ( 'Table (2)'[Units] ),
        ALL ( Dates ),
        Dates[Date] <= SelectedMaxDate,
        Dates[Date] >= MinDate
        )/3
         and produces this:

         

        but unfortunatly it is not what you want.