Forum Discussion
Exponential Weighted Moving Average without Time Intelligence
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,
Pavan
Anonymous @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,
PavanHi danextian thank you for sending across the pbix to me. I have tried replicating your method within my dataset but it doesnt seem to work. I think it may be due to the recursive part of the EWMA formula, where the later part is looking back at the previous EWMA to calculate the current value. I'll keep trying to find a solution.
Based on my research my dataset seems quite different to how others are using Power BI. The dataset I have is created by 20 tables being pulled from a database made up of both DIM and FCT tables which are joined on a unique key. Most people seem to have their data self contained in one spreadsheet.