moving average over 26 weeks

[emmaclarke83]

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.

[emmaclarke83]

Hi

Can anyone else help with this?

Thanks

EMma

[jthomson]

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]

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]

@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

[emmaclarke83]

Hi Charlie Thanks for your reply. I think this is close to what I need but I need it for each employee and when I add the employee number field the calculations don't look correct. Is there anything else I can do? Thanks Emma