Rolling average for the last 4 weeks average value.

[Anonymous]

Hi All,

Need your support.

I want to calculate moving/rolling average value of a KPI for the last 4 weeks average values using DAX.

I have simply calculated the values in excel for the reference and attached the Snapshot as well.

Please consider the values and help to get the desired ouput.

[johnt75 · Accepted solution]

You'll need a week commencing column on your date table, or something similar, then 

Weekly Avg = 
VAR currentWC =
    MAX ( 'Date'[W / C] )
VAR startDate = currentWC - 35
VAR endDate = currentWC - 1
RETURN
    AVERAGEX (
        ADDCOLUMNS (
            CALCULATETABLE (
                SUMMARIZE ( 'Date', 'Date'[W / C] ),
                DATESBETWEEN ( 'Date'[Date], startDate, endDate )
            ),
            "@val", [Measure]
        ),
        [@val]
    )