Forum Discussion
emmaclarke83
Helper I
Helper Imoving 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 no...
v-caliao-msft
Microsoft Employee
Microsoft Employee
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
emmaclarke83Helper I
Helper IHi 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