cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

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

1 ACCEPTED SOLUTION
Community Support

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

7 REPLIES 7
Community Support

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

Super User

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``````

Super User

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.

Helper II

Dynamic based on the current year.

Super User

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.

Helper II

the previous two years are dynamic

Helper II

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.