Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Average per ID using data from last 3 years

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(**

Need Help
  • ERD's avatar
    ERD
    5 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

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

  • ERD's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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.