Forum Discussion

gianma78's avatar
gianma78
Frequent Visitor
2 years ago

Help with Weighted Moving Average for delay delivery

Dear Community, hope you can assist. I am struggling with finding the correct way to calculate in Power Bi the Weighted Moving Average for delay delivery.  Here is the case, assuming that we are ru...
Need Help
gianma78's avatar
gianma78
Frequent Visitor
2 years ago

Hi Ryan,
apologies for the late reply, really sorry for that. 
Please see below a better example:

I would like to calculate in DAX the weighted moving average of last 6 periods:

avg=(M1*1+M2*2+M3*3+M4*4+M5*5+M6*6)/21

(M1: 6 months ago, M2: 5 months ago, M3: 4 months ago, ..., M6: previous month).

Each month can have no feedback, one feedback or more.

Therefore I need first aggregate the feedback received per month and average them.

Expected result is below

wtRow LabelsAverage of Feedbackwma
1Jan-228.00 
2Feb-2210.00 
3Apr-229.00 
4Aug-2210.00 
5Sep-2210.00 
6Oct-229.009.48
 Nov-2210.009.67
 Dec-223.007.76
 Feb-2310.008.19
 Aug-237.507.86
 Sep-238.007.79
 Oct-239.008.10
 Jan-242.006.40
 Feb-248.006.81
 Mar-2410.007.55
 Apr-249.008.00
 May-249.008.38
 Jun-249.678.90

 

where wma is =SUMPRODUCT(C2:C78,$A$2:$A$7)/SUM($A$2:$A$7)

 

I have tried this solution, but it does not return expected result

Test_wma =

var current_month = MAX('Feedback'[Date])

var m1 =
CALCULATE(AVERAGE('Feedback'[Rating]), 'Feedback'[Date] = edate (current_month,-1))

var m2 =
CALCULATE(AVERAGE('Feedback'[Rating]), 'Feedback'[Date] = EDATE(current_month, -2))

var m3 =
CALCULATE(AVERAGE('Feedback'[Rating]), 'Feedback'[Date] = EDATE(current_month, -3))

var m4 =
CALCULATE(AVERAGE('Feedback'[Rating]), 'Feedback'[Date] = EDATE(current_month, -4))

var m5 =
CALCULATE(AVERAGE('Feedback'[Rating]), 'Feedback'[Date] = EDATE(current_month, -5))

var m6 =
CALCULATE(AVERAGE('Feedback'[Rating]), 'Feedback'[Date] = EDATE(current_month, -6))

var wma = ((m6 * 1) + (m5 * 2) + (m4 * 3 ) + (m3 * 4 ) + (m2 * 5) + (m1 * 6)) /21

RETURN wma
 
Any suggestion is highly appreciated!
Many thanks!
foodd's avatar
foodd
Icon for Community Champion rankCommunity Champion
2 years ago

Hello gianma78 , as ryan_mayu has asked , please help with the following:

.
Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.