How to find the monthly average over multiple years

[Anonymous]

I have sales dating back 5 years.

I would like to find the 5 year January average, February average, etc.

Thanks!

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

You are trying to get the average of each month for the last five years right?
Why not create a matrix and change the way the values are aggregated to the average, which doesn't even require adding any additional columns or measures.

Please correct me if I didn't understand the question correctly and consdier sharing more details about it or a simple sample file without any sesentive information for further discussion.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataInsights]

@Anonymous,

Try this measure. It also works with datasets that contain more than five years:

Five Year Average = 
VAR vMaxYear =
    CALCULATE ( MAX ( Table1[Year] ), ALL ( Table1[Year] ) )
VAR vResult =
    CALCULATE (
        AVERAGE ( Table1[Amount] ),
        ALL ( Table1[Year] ),
        Table1[Year] >= vMaxYear - 4
    )
RETURN
    vResult