Average per ID using data from last 3 years

[Anonymous]

19I have a table with data por ID and sales per month Like this

I need to create a calculated column with average per ID per month for each id like this

I used that but it appears circular dependency.

Average_last_ 3_year =

VAR year_ref =

    YEAR ( T_[date])

VAR month_ref =

    MONTH ( T_[date])

RETURN

    IF (

        year_ref - 2

            >= MINX ( ALL ( T_[date]), YEAR ( T_[date]) ),

        AVERAGEX (

            FILTER (

                ALLEXCEPT(  T_, T[Id]),

                YEAR (( T_[date]) ) < year_ref

                    && YEAR (( T_[date]) >= year_ref - 3

                    && MONTH (( T_[date]) ) = month_ref

            ),

            T_[Valor]

        )

    )

What is it wrong(**

[ERD · Accepted solution]

Hi @Anonymous ,

You need to change your column formula a bit:

Column =
VAR currYear = YEAR ( T[Date] )
VAR currMonth = MONTH ( T[Date] )
VAR currID = T[Id]
RETURN
    IF (
        currYear - 2 > MINX ( ALL ( T ), YEAR ( T[Date] ) ),
        AVERAGEX (
            FILTER (
                T,
                T[Id] = currID
                    && YEAR ( T[Date] ) < currYear
                    && YEAR ( T[Date] ) >= currYear - 3
                    && MONTH ( T[Date] ) = currMonth
            ),
            T[Value]
        )
    )

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

[amitchandak]

@Anonymous , Try a new column like

New column =

var _month = month([Date])

var _id = [id]

return

averagex(filter(Table, [id]=_id  && month([Date])  =_month ), [value])

[Anonymous]

I need to calculate the average using always data from last 3 years.