average count of 3 months for every month in matrix.

[rishabjain237]

Hi guys,
I need to caclulate the average count of 3 months for every month in a matrix.

For example : For August month The numbers which should be displayed is the average of June+july+august. and for july it should be July+june+may and so on. Please provide a measure for this. 
The columns Iam using :
'Master append'[Groups] - in Rows
'Master append'[Month] - in Columns
'Measures Table'[TotalLeadsForSales] - in values. where,

TotalLeadsForSales = CALCULATE(COUNT('Master append'[Lead_Type]),'Master append'[lang]="English",'Master append'[Lead_Type]="Business")
Lead_type was used because its a column with no null values.

Thanks and Regards.
Rishab Jain.

[parry2k]

@rishabjain237 I think you need this:

Test 3 =
VAR numofmonths = 3
VAR LastSelectedDate = MAX('Master append'[Date])
VAR Period =
    DATESINPERIOD('Master append'[Date],LastSelectedDate,-numofmonths,MONTH)
VAR Result =
    CALCULATE(
            AVERAGEX(
                VALUES('Master append'[Month]),
                [TotalLeadsForSales]
            ),
            Period
     )
Return
Result

[rishabjain237 · Accepted solution]

Hello sir @parry2k , Thanks for the reply.
I have tried the given code but problem still remains.

The average is only displayed in totals column. I want to display the average of 3 month(includes present month) for every month. For example the august column should have the average of jun july aug and the july should have avg of july june and may.

[rishabjain237]

I have tried to this formula :

Test 3 =

VAR numofmonths = 3

VAR LastSelectedDate = MAX('Master append'[Date])

VAR Period =

    DATESINPERIOD('Master append'[Date],LastSelectedDate,-numofmonths,MONTH)

VAR Result =

    CALCULATE(

            AVERAGEX(

                VALUES('Master append'[Month]),

                MeasuresTable[TotalLeadsForSales]),Period)

Return

Result
But the average is being displayed in totals column :

@parry2k @amitchandak Please need your advice in this. amit sir I have seen your weekly comparison videos. it was very help full. Thanks!