Calculate average of same day across each year in data set

[smsat]

I have a data set of 6 years of data. 

I want to create a column to calculate the average of "% of EOM" for each of the 6 years on that day.

e.g. for 31st December it would calc the average of % of EOM across these dates 31/12/18, 31/12/17, 31/12/16, 31/12/15. 31/12/14, 31/12/13 

I've been scratching my head and can't work it out - any help much appreciated!

[v-yulgu-msft]

Hi @smsat ,

Please try this measure:

Average =
CALCULATE (
    AVERAGE ( Table[% of EOM] ),
    ALLEXCEPT ( Table, Table[Date].MONTH, Table[Date].DAY )
)

Best regards,

Yuliana Gu

[smsat]

Thanks very much for the reply @v-yulgu-msft 

That hasn't quite worked.  It's produced the same % in every row, I can't quite work out what it has averaged to get to 55.79%.

Ideally what I'm looking for it to do is for each row to average all other years on that date, e.g. on any row with date 31 December it will average

31/12/18  102.6%

31/12/17  62.8%

31/12/16 66.9%

31/12/15  73% 

31/12/14   70%

31/12/13   82.2%

and the "This Date Average" column for every 31st December will show = 76.25%

This value will be different for 30 December, 29 December, etc.

[v-yulgu-msft · Accepted solution]

Hi @smsat ,

AVG_%_of_EOM =
CALCULATE (
    AVERAGE ( Test_2[% of EOM] ),
    FILTER (
        ALLSELECTED ( Test_2 ),
        FORMAT ( Test_2[Date], "MMM dd" ) = FORMAT ( MAX ( Test_2[Date] ), "MMM dd" )
    )
)

Best regards,

Yuliana Gu

[smsat]

Excellent, thank you very much indeed.