How can moving average include complete 13 weeks in serious

[JOHN-jnpo]

Hello,

I got a problem is 13 weeks moving average only the last week is correct with complete 13 weeks data.

the others week is not complete 13 weeks while I using OVER (LastPeriods(13,[Axis.X])) and visualization has 13 weeks in axis X.

What I need is visualize a 13 weeks moving average and every weeks are calculated with complete 13 weeks even the previously week are not show in visualization.

Example as below, only wk2416 has calculated by 13 weeks, and wk2404 only has calculated by we2404.

May someone help to resolve this kind problem? Thank you in advance.

 

[David Boot-Olazabal]

Hi John,

Trying to understand the problem.
Your weeks from 2404-2415 only have the individual values, whereas the week 2416 holds all the 13 weeks. Is that correct?

If so, you want to use a formula to calculate the moving average of 13 weeks, which typically can be done via this formula:
Sum([ORDER_AMOUNT]) THEN Avg([Value]) OVER (LastPeriods(13,[Axis.X])) THEN If(Count() OVER (LastPeriods(13,[Axis.X]))=13,[Value],null).

But if the assumption is true, then you need to replace the 2416 value first with it's real value before applying a moving average.

Kind regards,

David

[Olivier Keugue Tadaa]

Hi John, I'd like to propose a different approach based on calculated columns. Indeed, when you use a custom expression it only applies to the filtered data on the visualization. Therefore, your first 12 periods will always show wrong values for moving averages when you filter your data.

Hence,

First, add the following columns 
YearWeek :::: YearAndWeek([week])
moving avg::::Sum([value]) over (LastPeriods(13,[YearWeek]))

Then, use the following Y-Axis custom expression ::::  Sum([moving avg])

you will have this

 

instead of that 

Note that both visualizations share the same filters 

don't forget to set the X-Axis as categorical like this...

Let me know if this helps please (I've attached my data source)

moving avg.xlsx

Edited May 16 by Olivier Keugue Tadaa

[JOHN-jnpo]

Hello David and Olivier,

I solved this problem by calculated column, indeed, I didn’t know the expression only apply on the visualization.

thank you for feedback!