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

Frequent Visitor

## Rolling average for weekly values

Hello,

my table with data has only two columns:

I would like to create a new table (visual) with rolling average measure:

week 9 = average for last 4 weeks

week 10 = average for last 4 weeks

.....

Thank you

2 ACCEPTED SOLUTIONS
Community Support

Hi, @marenecaCZ

You can try the following methods.
Measure:

``````4 week moving average =
Var _N1=SUMMARIZE(FILTER(ALL('Table'),[Week]<=MAX('Table'[Week])),[Week],"Sum",SUM('Table'[Count]))
Var _N2=TOPN(4,_N1,[Week],DESC)
Var _Average=DIVIDE(SUMX(_N2,[Sum]),4)
return
IF(COUNTX(_N2,[Sum])<4,BLANK(),_Average)``````

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

Frequent Visitor

Hello all,

I found the solution. In my data model, it looks like this:

Thank you all for your help.

Marek

8 REPLIES 8
Community Support

Hi, @marenecaCZ

You can try the following methods.
Measure:

``````4 week moving average =
Var _N1=SUMMARIZE(FILTER(ALL('Table'),[Week]<=MAX('Table'[Week])),[Week],"Sum",SUM('Table'[Count]))
Var _N2=TOPN(4,_N1,[Week],DESC)
Var _Average=DIVIDE(SUMX(_N2,[Sum]),4)
return
IF(COUNTX(_N2,[Sum])<4,BLANK(),_Average)``````

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

Frequent Visitor

Hello all,

I found the solution. In my data model, it looks like this:

Thank you all for your help.

Marek

Super User

Hello @marenecaCZ,

I think the following may do the trick:

``````Rolling Average =

VAR rollingValue = -3

RETURN
IF(
RANKX(
ALLSELECTED( 'Table'[Week] ),
CALCULATE( MAX( 'Table'[Week] ) ),
,
ASC
)
> ABS( rollingValue ),
AVERAGEX(
WINDOW(
rollingValue, REL,
0, REL,
ORDERBY( 'Table'[Week], ASC )
),
CALCULATE( SUM( 'Table'[Count] ) )
),
BLANK()
)``````

Let me know if this is ok.

Frequent Visitor

Hello @Alf94 , thank you, but it doesn't work. Something is wrong maybe in the ORDERBY function 😞

Marek

Super User

@marenecaCZ, can you share more details about what is not working please? It is working on my side, as you can see on this screenshot:

The ORDERBY function is underlined in red but works, this is a known bug.

Frequent Visitor

@Alf94 , something is wrong in my measure:

Super User

@marenecaCZ, the problem comes from the WINDOW() function. Your version of Power BI Desktop must be at least December 2022 to use this function, which doesn't seem to be the case now. Try upgrading your version and everything should be fine afterwards.

Frequent Visitor

@Alf94 thanks a lot. Unfortuntely I can't upgrade to Dec 2022 version due to some IT restrictions in the company where I work 😞 So I will have to look for another solution. But thanks a lot.

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.