Rolling 3 month average by person?

[Anonymous]

I have data that consists of persons name[Co-ordinator], month (dd/mm/yyyy format) [Month Completed] and the total completed tasks for that month [Count]. I have been asked to show the last 3 months average for each person. Can anyone please help with this as would be helpful to create other reports but for now I just need a bit of help achieving this. Thanks in advance.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[amitchandak]

@Anonymous , with help from a date table, view data by person

Rolling 3 = calculate(AverageX(Values('Date'[MONTH Year]),CALCULATE(sum(Sales[Sales Amount]))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

[Anonymous]

Hi and thanks for the reply. Can you explain what you mean please 'with help from a date table' ?

Also not quite sure how it determines when the data changes from one person to another?

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

Dax functions like DATESINPERIOD are time intelligence functions. There should be a related DimDate table with continuous dates when you use these functions.

I think you can try my code to create a measure without adding an related DimDate table.

My Sample:

Measure:

Rolling 3 month average by person = 
VAR _LASTMONTH =
    MAX ( 'Table'[Month] )
VAR _STARTMONTH =
    EOMONTH ( _LASTMONTH, -3 ) + 1
RETURN
    CALCULATE (
        AVERAGE ( 'Table'[Count] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Co-ordinator] ),
            'Table'[Month] >= _STARTMONTH
        )
    )

Result is as below.

Best Regards,
Rico Zhou

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