Weighted Moving Average

[seanmcc]

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

[v-zhenbw-msft]

Hi @seanmcc ,

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.

[seanmcc]

@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

[Greg_Deckler]

Can you post sample data in text so that we can experiment on it. I'm also not clear on what you are expecting as a result. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[seanmcc]

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