Weighted Moving Average

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.