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.

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.