Average per row based on slicer

[danielcontente]

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 be 487,003 (average of 2020 and 2021)

Thanks in advance 

[v-yangliu-msft · Accepted solution]

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]

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

[AlB]

I cannot copy the data from a picture

Dynamic based on what? Why not 2022 and 2021?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[danielcontente]

Dynamic based on the current year.

[AlB]

Hi @danielcontente 

1. Can you share a sample of the relevant tables in your model? Or, ideally, the pbix?

2. How are the "2 previous years" determined? Is it always 2020 and 2021 or dynamic?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[danielcontente]

the previous two years are dynamic

[danielcontente]

Hi, the table is in the first comment (there is a picture).