Weighted Moving Average

[ffan]

Hi,

I am currently migrating an Excel report over to PowerBi.

Currently, my chart has an x axis being the beginning date of each calendar week.

Each point is populated by a weighted moving average of the last 4 weeks.

My formula is Sumproduct: [last 4 data points, count of last 4 data points/ count of last 4 data points].

How would I code this in PowerBi?

Thank you

[v-juanli-msft]

Hi @ffan

Did my solution solve your problem?

If not, please don't hesitate to ask me.

Best Regards

Maggie

[v-juanli-msft · Accepted solution]

Hi @ffan

Assume your dataset has "date" and " data points" column as below, then I create columns to define which is the beginning date per week.

weekday = WEEKDAY([date],1)

weeknum = WEEKNUM([date])

beginning date each week = CALCULATE(MIN([date]),ALLEXCEPT(Sheet1,Sheet1[weeknum]))

Each point is populated by a weighted moving average of the last 4 weeks.

As known

Weighted MA(4) = F_t+1 = wt₁(D_t) + wt₂(D_t-1) + wt₃(D_t-2)+wt4(D_t-3)

for my example dataset from 2018/6/1~2018/8/13, it is from week 22 to week 33, so a weighted moving average of the last 4 weeks should calculate for week 30,31,32,33.

Assume D refers to total data per week,  wt refers to 0.1,0.2,0.3,0.4.

total data per week shoud be get from a calculated measure

total per week = CALCULATE(SUM(Sheet1[data points]),FILTER(ALL(Sheet1),[weeknum]=SELECTEDVALUE(Sheet1[weeknum])))

Till now, I have doubt which is your D  and wt refer to, and whether my understanding above is correct, please let me know more.

Besides, which does the formula mean?

[last 4 data points, count of last 4 data points/ count of last 4 data points]

Best Regards

maggie