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
danextian's avatar
danextian
Super User
1 year ago

Hi richardburling 

 

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

  • richardburling's avatar
    richardburling
    Helper I
    1 year ago

    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 YearWeek NumberWeek OffsetSales (£)EWMA
    2023-241-75494718443799
    2023-242-74385830438002
    2023-243-73588634453065
    2023-244-72443846452143
    2023-245-71493271456256
    2023-246-70394481450078
    2023-247-69407996445870
    2023-248-68369682438251
    2023-249-67680507462477
    2023-2410-66315483447777
    2023-2411-65356677438667
    2023-2412-64415639436365
    2023-2413-63622488454977
    2023-2414-62386950448174
    2023-2415-61360596439416
    2023-2416-60486936444168
    2023-2417-59468579446609
    2023-2418-58615847463533
    2023-2419-57450311462211
    2023-2420-56365655452555
    • 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.