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 reference and attached the Snapshot as well.

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

 

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

1 Reply

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