Forum Discussion

PrachiD's avatar
PrachiD
Helper I
8 years ago
Solved

Calculate Moving Average

 

Hello,

Need help for the following
I want to calculate average colunm as show in the above fig.

The formula to calculate the Average is for April month it will remain as it is but for May it should calculate likewise :- count of april + count of May Divide by 2 ( e.g(5+10)/2) so that i will get the value as 7.5

for june it should calculate likewise:- count of april + count of May+count of June  Divide by 3 ( e.g(5+10+15)/3) so that i will get the value as 10.

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

4 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    HI PrachiD

     

    Try this

     

    Try this solution.

    If you donot have a column for Month Numbers... add this calculated column

     

    Month_Number =
SWITCH (
    TableName[Month],
    "January", 1,
    "February", 2,
    "March", 3,
    "April", 4,
    "May", 5,
    "June", 6,
    "July", 7,
    "August", 8,
    "September", 9,
    "October", 10,
    "November", 11,
    "December", 12
)

     

    Now you can use this MEASURE

     

    Average =
CALCULATE (
    AVERAGE ( TableName[Count] ),
    FILTER (
        ALL ( TableName ),
        TableName[Month_Number] <= SELECTEDVALUE ( TableName[Month_Number] )
    )
)