Build a column with average of last 2 year

[Anonymous]

Hi all,

I have a table like:

I need to add using dax, an column with average of last 2 year like this:

Do someone know if this it possible to do?

Thanks

[MFelix · Accepted solution]

Hi @Anonymous ,

I believe that being an average you would be better making a measure however try the following column:

Average_last_ 2_year = 
VAR year_ref =
    YEAR ( 'Table'[Date] )
VAR month_ref =
    MONTH ( 'Table'[Date] )
RETURN
    IF (
        year_ref - 2
            >= MINX ( ALL ( 'Table'[Date] ), YEAR ( 'Table'[Date] ) ),
        AVERAGEX (
            FILTER (
                ALL ( 'Table' ),
                YEAR ( 'Table'[Date] ) < year_ref
                    && YEAR ( 'Table'[Date] ) >= year_ref - 2
                    && MONTH ( 'Table'[Date] ) = month_ref
            ),
            'Table'[Value]
        )
    )