Calculate Moving Average

[PrachiD]

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.

[Zubair_Muhammad]

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] )
    )
)

[Zubair_Muhammad · Accepted solution]

@PrachiD

[PrachiD]

Count in the above column is of Person ID how can I manage Average and Count in same measure or Column because I want distinct count for Person ID with Moving Average

[PrachiD]

Thank you