Forum Discussion
Exponential Weighted Moving Average without Time Intelligence
Good Morning,
I hope you are all well. 😁
I have been trying to calculate you a Exponential Weighted Moving Average based on sales data, but unfortunately I dont have calendar date in my data as its based on Financial Year (FY) and Week No. The request from a client is to be able to have the EWMA reported by Week No but can bridge FY based on a the selections made using slicers. The week number ranges from 1 to 52 for every FY.
The sales data is in a seperate table to both the FY and Week No, these are joined by a key and the data is refreshed on a weekly basis. There is also a column called Week Offset, this start at the latest week and is marked as 0, the reason for this is that the Calendar table is extracting data from a database that has future dates so this may need to be used as I cant have data showing before the latest week.
I am unable to attach a spreadsheet but here is an exmaple.
Any help or guidance on this would be appreciated.
| Financial Year | Week Number | Week Offset | Sales (£) |
| 2023-24 | 43 | -33 | 536511 |
| 2023-24 | 44 | -32 | 566096 |
| 2023-24 | 45 | -31 | 608640 |
| 2023-24 | 46 | -30 | 490213 |
| 2023-24 | 47 | -29 | 543834 |
| 2023-24 | 48 | -28 | 500859 |
| 2023-24 | 49 | -27 | 575474 |
| 2023-24 | 50 | -26 | 361348 |
| 2023-24 | 51 | -25 | 480139 |
| 2023-24 | 52 | -24 | 545687 |
| 2024-25 | 1 | -23 | 461591 |
| 2024-25 | 2 | -22 | 601092 |
| 2024-25 | 3 | -21 | 541020 |
| 2024-25 | 4 | -20 | 435140 |
| 2024-25 | 5 | -19 | 234882 |
| 2024-25 | 6 | -18 | 222291 |
| 2024-25 | 7 | -17 | 386150 |
| 2024-25 | 8 | -16 | 321166 |
| 2024-25 | 9 | -15 | 439572 |
| 2024-25 | 10 | -14 | 313679 |
| 2024-25 | 11 | -13 | 389012 |
| 2024-25 | 12 | -12 | 493415 |
| 2024-25 | 13 | -11 | 461943 |
| 2024-25 | 14 | -10 | 432882 |
| 2024-25 | 15 | -9 | 456854 |
| 2024-25 | 16 | -8 | 446320 |
| 2024-25 | 17 | -7 | 396670 |
| 2024-25 | 18 | -6 | 518345 |
| 2024-25 | 19 | -5 | 451277 |
| 2024-25 | 20 | -4 | 523998 |
| 2024-25 | 21 | -3 | 511155 |
| 2024-25 | 22 | -2 | 423883 |
| 2024-25 | 23 | -1 | 302786 |
| 2024-25 | 24 | 0 | 158644 |
Thanks RB.
9 Replies
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,
Pavan
Hi richardburling
I wanted to follow up, We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Answer" and select "Yes" if it was helpful.If you need any further assistance, feel free to reach out.
Thank you,
Pavan.Hi richardburling,
I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.
Thank you,
Pavan.
