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 circu...
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.

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.