cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
emmaclarke83 Regular Visitor
Regular Visitor

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.

5 REPLIES 5
jthomson New Contributor
New Contributor

Re: moving average over 26 weeks

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 Regular Visitor
Regular Visitor

Re: moving average over 26 weeks

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.

 

Moderator v-caliao-msft
Moderator

Re: moving average over 26 weeks

@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

Capture.PNG

 

Regards,

Charlie Liao

emmaclarke83 Regular Visitor
Regular Visitor

Re: moving average over 26 weeks

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

 

 

emmaclarke83 Regular Visitor
Regular Visitor

Re: moving average over 26 weeks

Hi

Can anyone else help with this?

Thanks

 

EMma

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors