cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

6 REPLIES 6
Frequent Visitor

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

 wt Row Labels Average of Feedback wma 1 Jan-22 8.00 2 Feb-22 10.00 3 Apr-22 9.00 4 Aug-22 10.00 5 Sep-22 10.00 6 Oct-22 9.00 9.48 Nov-22 10.00 9.67 Dec-22 3.00 7.76 Feb-23 10.00 8.19 Aug-23 7.50 7.86 Sep-23 8.00 7.79 Oct-23 9.00 8.10 Jan-24 2.00 6.40 Feb-24 8.00 6.81 Mar-24 10.00 7.55 Apr-24 9.00 8.00 May-24 9.00 8.38 Jun-24 9.67 8.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!
Super User

Hi,

PBI file attached.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

.
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-...

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

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

Super User

could you pls provide some sample data and expected output?

Proud to be a Super User!

Frequent Visitor

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 Delivery Project Rating 06/2023 P23_001 7 06/2023 P23_002 5 08/2023 P23_003 9 09/2023 P23_004 8 12/2023 P23_005 01/2024 P24_001 6 01/2024 P24_002 9

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
Super User

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

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors