Forum Discussion

emmaclarke83's avatar
emmaclarke83
Helper I
8 years ago

moving average over 26 weeks

I have a dataset which details the hours worked for every employee each week of the year.  I want to calculate a moving average over the past 26 weeks.  Is this possible?  Note, my date table does not have individual dates as the main dataset is in weeks.

Need Help

5 Replies

  • jthomson's avatar
    jthomson
    Solution Sage
    8 years ago

    How is the week value stored, is it just an integer, and does it reset yearly? Assuming yes and yes, I'd try looking up solutions on how people have created a yearmonth column in a date table - if you can do similar and end up with something like 201652 for the last week of last year and 201701 for the first week of this year, then you've got a single integer where the highest values are the most recent. It should be straightforward to use something like TOPN(26) as a filter for an average on your hours column

    • emmaclarke83's avatar
      emmaclarke83
      Helper I
      8 years ago

      excel sample

       

      Hi, yes an integer.


      I can make a year/week column no problem. However, the top26 will not work as this ia a moving average. I have attached a link above to an example Excel to show you what I mean.

       

      • v-caliao-msft's avatar
        v-caliao-msft
        Microsoft Employee
        8 years ago

        emmaclarke83,

         

        You could use the measure below to get moving average over 26 weeks.

        26WeekMovingSumHour =
VAR minweeknumber =
    MAX ( Table1[Weeknumber] ) - 26
VAR maxweeknumber =
    MAX ( Table1[Weeknumber] )
RETURN
    CALCULATE (
        Table1[TotalWeekHour],
        FILTER (
            ALL ( Table1 ),
            Table1[Weeknumber] > minweeknumber
                && Table1[Weeknumber] <= maxweeknumber
        )
    )

        numberofweeks =
VAR minweeknumber =
    MAX ( Table1[Weeknumber] ) - 26
VAR maxweeknumber =
    MAX ( Table1[Weeknumber] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Table1[Weeknumber] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Weeknumber] > minweeknumber
                && Table1[Weeknumber] <= maxweeknumber
        )
    )

        26WeekMovingAverageHour =
VAR minweeknumber =
    MAX ( Table1[Weeknumber] ) - 26
VAR maxweeknumber =
    MAX ( Table1[Weeknumber] )
VAR numberofWeek =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Weeknumber] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Weeknumber] > minweeknumber
                && Table1[Weeknumber] <= maxweeknumber
        )
    )
VAR WeekMovingSumHour =
    CALCULATE (
        Table1[TotalWeekHour],
        FILTER (
            ALL ( Table1 ),
            Table1[Weeknumber] > minweeknumber
                && Table1[Weeknumber] <= maxweeknumber
        )
    )
RETURN
    WeekMovingSumHour / numberofWeek


         

        Regards,

        Charlie Liao