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 running different project, and for each project we have expected delivery date. If that date is missed we have delay. Need now to calculate the weighted moving average for this delays in PBI.

Is that possible? 

Thanks for help!

 

Need Help

6 Replies

    • gianma78's avatar
      gianma78
      Frequent Visitor
      2 years ago

      Hello Ryan, sorry for the late reply. In the meantime, requestor changed the specifics for this calc.

      What we aim to obtain is the weighted moving average per scores, and the moving should be based on a fix amount of months (or rating received). 

      Assuming the following example

      Date of DeliveryProjectRating
      06/2023P23_0017
      06/2023P23_0025
      08/2023P23_0039
      09/2023P23_0048

      12/2023

      		P23_005 
      01/2024P24_0016
      01/2024P24_0029

       

      It is a sample of the table I have, I can calculate the moving average as following DAX

      "CALC Rating Moving Avg =
      VAR _max_date = ENDOFMONTH('Calendar'[Date])
      VAR _min_date = EDATE(_max_date,-6)
      VAR _avg = CALCULATE(AVERAGE('Projects Log '[Feedback]),'Calendar'[Date] > _min_date && 'Calendar'[Date] <= _max_date)
      RETURN _avg "
       
      Any chance to have the weighted moving avg to have more emphasys to the recent Project delivered (guess I need to create the table of weights which is rolling on monthly basis)? And, if no rate is received, then skip the project from calculation 
      Any hint would be highly appreciated
      • ryan_mayu's avatar
        ryan_mayu
        Super User
        2 years ago

        what's the expected output basd on the sample data you provided?

         

  • 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!