Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Rolling average for the last 4 weeks average value.

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 referenc...
Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    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]
    )
johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

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]
    )