Help with Weighted Moving Average for delay delivery

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

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!

Forum Discussion