Forum Discussion

Noki's avatar
Noki
Regular Visitor
4 years ago

Moving average - weekly

Hi! Data on receivables arrive on a specific day of the week. How can I create a variable weekly moving average?

Thx

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Noki,

    You can write a measure expression and use a variable to store the current date, then you can use the year and weeknum function with date values as conditions to filter and calculate the rolling average based on the week.

    formula =
VAR currDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        AVERAGE ( Table[Sales] ),
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( [Date] ) = YEAR ( currDate )
                && WEEKNUM ( [Date], 2 ) = WEEKNUM ( currDate, 2 )
                && [Date] <= currdate
        ),
        VALUES ( Table[Category] )
    )

    Regards,

    Xiaoxin Sheng

    • Noki's avatar
      Noki
      Regular Visitor
      4 years ago

      Where can I enter how many weekly averages to calculate?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        HI Noki,

        My formula is calculated on the current week rolling daily average, if you mean calculating across multiple weeks, you can try to use the following formulas.

        formula =
VAR currDate =
    MAX ( Table[Date] )
VAR currCategory =
    SELECTEDVALUE ( Table[Category] )
VAR weekRange = 2
VAR prevDate =
    DATE ( YEAR ( currDate ), MONTH ( currDate ), DAY ( currDate ) - 7 * weekRange )
VAR filtered =
    FILTER ( Table, [Date] >= prevDate && [Category] = currCategory )
VAR summary =
    SUMMARIZE (
        ADDCOLUMNS (
            filtered,
            "Year", YEAR ( Table[Date] ),
            "Week Number", WEEKNUM ( Table[Date], 2 )
        ),
        [Year],
        [Week Number],
        "WeeklySales", SUM ( Table[Sales] )
    )
RETURN
    AVERAGEX ( summary, [WeeklySales] )

        Regards,

        Xiaoxin Sheng

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Noki , if you need across week

     

    Have week rank in your date or week table, prefer a separate table

     

    Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
    OR
    Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

     

    measure

    Last 2 weeks = CALCULATE(AverageX(values('Date'[Week Rank]), calculate(sum('Table'[Qty]))) , FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-3 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

     

     

    If you data at date level then  , Avg Above day level

     

    AverageX(values('Date'[Date]), calculate(sum('Table'[Qty])))