Forum Discussion
Weighted Moving Average
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
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) = Ft+1 = wt1(Dt) + wt2(Dt-1) + wt3(Dt-2)+wt4(Dt-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
2 Replies
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) = Ft+1 = wt1(Dt) + wt2(Dt-1) + wt3(Dt-2)+wt4(Dt-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
Hi ffan
Did my solution solve your problem?
If not, please don't hesitate to ask me.
Best Regards
Maggie