Exponential Weighted Moving Average without Time Intelligence

[richardburling]

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.

[Anonymous]

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.

[Anonymous]

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.

[danextian]

Hi @richardburling 

Not everyone is familiar with EWMA. Please provide expected result from the sample data and the reasoning behind.

[richardburling]

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

[Anonymous]

Hi @richardburling 

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

[richardburling]

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

[Anonymous]

Hi @richardburling 

Please find the attached PBIX file below for your reference

Thanks,
Pavan

[richardburling]

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

[Anonymous]

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.

Thanks,
Pavan.