Forum Discussion

danielcontente's avatar
danielcontente
Icon for Helper II rankHelper II
3 years ago
Solved

Average per row based on slicer

Hi all,    I have created a table and would like to create a measure that will average the values per row for the 2 previous years. In the below table, the result for WEEK 41, for example, would b...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  danielcontente ,

     

    Here are the steps you can follow：

    1. Create measure.

    Measure =
var _today=YEAR(TODAY())
var _1year=
SUMX(FILTER(ALL('Table'),'Table'[Year]=_today -1&&'Table'[Weeknumber]=MAX('Table'[Weeknumber])),[Value])
var _2year=
SUMX(FILTER(ALL('Table'),'Table'[Year]=_today -2&&'Table'[Weeknumber]=MAX('Table'[Weeknumber])),[Value])
return
DIVIDE(
    _1year+_2year,2)

    2. Result:

     

    Best Regards,

    Liu Yang

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

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

hi danielcontente 

try like:

Average = 
VAR _2020 = CALCULATE(SUM(TableName[Value]), YEAR(TableName[Date])=2020)
VAR _2021 = CALCULATE(SUM(TableName[Value]), YEAR(TableName[Date])=2021)
RETURN
(_2020+_2021)/2