Forum Discussion
Exponential Weighted Moving Average without Time Intelligence
Not everyone is familiar with EWMA. Please provide expected result from the sample data and the reasoning behind.
Hi danextian, thanks for taking the time to response. I have provided an update version of the table with the EWMA added. The first value, highlighted in red, is the simple average of the previous 52 weeks. The excel EWMA formula that I was provided and asked to replicate in Power BI is =(sales*alpha)+((1-alpha)*Previous EWMA). The alpha is set to 0.1 but this will like need to be dynamic or changeable on a filter.
| Financial Year | Week Number | Week Offset | Sales (£) | EWMA |
| 2023-24 | 1 | -75 | 494718 | 443799 |
| 2023-24 | 2 | -74 | 385830 | 438002 |
| 2023-24 | 3 | -73 | 588634 | 453065 |
| 2023-24 | 4 | -72 | 443846 | 452143 |
| 2023-24 | 5 | -71 | 493271 | 456256 |
| 2023-24 | 6 | -70 | 394481 | 450078 |
| 2023-24 | 7 | -69 | 407996 | 445870 |
| 2023-24 | 8 | -68 | 369682 | 438251 |
| 2023-24 | 9 | -67 | 680507 | 462477 |
| 2023-24 | 10 | -66 | 315483 | 447777 |
| 2023-24 | 11 | -65 | 356677 | 438667 |
| 2023-24 | 12 | -64 | 415639 | 436365 |
| 2023-24 | 13 | -63 | 622488 | 454977 |
| 2023-24 | 14 | -62 | 386950 | 448174 |
| 2023-24 | 15 | -61 | 360596 | 439416 |
| 2023-24 | 16 | -60 | 486936 | 444168 |
| 2023-24 | 17 | -59 | 468579 | 446609 |
| 2023-24 | 18 | -58 | 615847 | 463533 |
| 2023-24 | 19 | -57 | 450311 | 462211 |
| 2023-24 | 20 | -56 | 365655 | 452555 |
Thanks for reaching out to the Microsoft Fabric Community Forum
In response to your query, here is the DAX formula:
EWMA =
VAR PreviousEWMA =
CALCULATE(
MAX(Sales_data[Sales]),
FILTER(Sales_data, Sales_data[Week Offset] = EARLIER(Sales_data[Week Offset]) - 1)
)
RETURN
IF(
ISBLANK(PreviousEWMA),
(Sales_data[Sales] * 0.1) + ((1 - 0.1) * 494718),
(Sales_data[Sales] * 0.1) + ((1 - 0.1) * PreviousEWMA)
)Here is the sample output for your review.
If this post meets your requirements, please consider giving us Kudos. Should you need further assistance, kindly provide more details about your scenario and attach a sample Pbix file.
If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.
Thanks,
PavanAnonymous @Thanks for taking the time to respond to my request. For some reason when applying the DAX in to my PBIX file, it doesn't recognise the EARLIER function. Are you able to upload your pbix file so I can see how you got it to work.
Hi richardburling
Please find the attached PBIX file below for your reference
Thanks,
Pavan