Forum Discussion

richardburling's avatar
richardburling
Helper I
1 year ago

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
richardburling
Helper I
1 year ago

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's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi richardburling 

    Please find the attached PBIX file below for your reference

    Thanks,
    Pavan

    • richardburling's avatar
      richardburling
      Helper I
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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.