Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Weighted Moving Average

Hi all,

 

I have been trying to calculate an exponentially weighted moving average for a 7 day period and a 28 day, however I am struggling to pick out the row context in my DAX. 

I have listed my measures in the image attached.

I need to be able to identify the value for total distance within my DAX.

 

I appreciate any help on this.

Thanks in advance.

 

Sean

 

Need Help

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Greg_Deckler 

       

      I have provided a sample of my data table 'GPS Data Unpivoted'.

      This would have multiple players across multiple dates and metrics.

      Sample data excel
       
      Below is the exponentially weighted average calculation that I am trying to recreate.
      EWMA(t) = a * x(t) + (1-a) * EWMA(t-1)

      Where

      • EWMA(t) =  moving average at time t
      • a = degree of mixing parameter value between 0 and 1
      • x(t) = value of signal x at time t

      I am trying to create this calculation over a 7 day period and a 28 day period.

       

      Using the example of a 7 day EWMA;

      a = 2/(7+1)

      x(t) = would be the value for each day (including days where players have no data)

      EWMA(t-1) = the EWMA rolling average for previous day

       

      Within the sample data I have created an EWMA 7 day column which is my expceted results.

       

      I hope that helps a bit more?

       

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    We can use the following steps to meet your requirement.

     

    1. Create a Rolling Average 7 day column.

     

    Rolling Average 7 day = 
var current_ = 'Table'[Date]
var last_7 = current_ - 7
return
IF(
    current_-MIN('Table'[Date])>=6,
CALCULATE(AVERAGE('Table'[Value]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=current_&&'Table'[Date]>last_7)),BLANK())

     

     

    2. Then create a EWMA 7 day column, we can get the result.

     

    EWMA 7 day column = 
VAR currentDate = 'Table'[Date]
VAR minDate =
    MIN ( 'Table'[Date] ) + 6
VAR result =
    SUMX (
        FILTER ( 'Table', 'Table'[Date] > minDate && 'Table'[Date] < currentDate ),
        [7 days weighting] * [Value]
            * POWER ( 1-[7 days weighting], DATEDIFF ( [Date], currentDate, DAY ) )
    )
        + CALCULATE (
            SUM ( 'Table'[Rolling Average 7 day] ),
            'Table',
            'Table'[Date] = minDate
        )
            * POWER ( 1-[7 days weighting], DATEDIFF ( minDate, currentDate, DAY ) ) + [Value] * [7 days weighting]
RETURN
IF (
        currentDate < minDate,
        BLANK (),
        IF ( currentDate = minDate, [Rolling Average 7 day], result )
)

     

     

    3. Or we also can create a measure to get the same result.

     

    EWMA 7 day Measure = 
VAR currentDate = MAX('Table'[Date])
VAR minDate =
    CALCULATE(MIN ( 'Table'[Date] ),ALLSELECTED('Table')) + 6
VAR result =
    SUMX (
        FILTER ( ALLSELECTED('Table'), 'Table'[Date] > minDate && 'Table'[Date] < currentDate ),
        [7 days weighting] * 'Table'[Value]
            * POWER ( 1-[7 days weighting], DATEDIFF ( [Date], currentDate, DAY ) )
    )
        + CALCULATE (
            SUM ( 'Table'[Rolling Average 7 day] ),
            ALLSELECTED('Table'),
            'Table'[Date] = minDate
        )
            * POWER ( 1-[7 days weighting], DATEDIFF ( minDate, currentDate, DAY ) ) + CALCULATE(SUM('Table'[Value])) * [7 days weighting]
RETURN
IF (
        currentDate < minDate,
        BLANK (),
        IF ( currentDate = minDate, CALCULATE(SUM([Rolling Average 7 day])), result )
)

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-zhenbw-msft Thanks so much for the reply.

       

      Firstly on reviewing my example data, the results I am expecting were incorrect, so apologies.

      Please see linked below;

      Expected results & pbix 

       

      I have already created measures for a 7 day rolling average. I have used your measure provided and manipulated the DAX to include the measure rather than column but I am not getting the expected result.

       

      Thanks for your help

      Sean