Average of previous 4 week starts

[Anonymous]

Hi All,

I have data like below for every week start and hours.

Name	Week Start	Hours
A	02/10/2020	30
A	02/17/2020	40
A	02/24/2020	25
A	03/02/2020	40
A	03/09/2020	0
A	03/16/2020	0
A	03/23/2020	0
A	03/30/2020	0
A	04/06/2020	0

So the requirement is. If Hour is zero, then calculate average Hours of previous 4 week starts.

Thanks in advance!!

[V-pazhen-msft · Accepted solution]

@Anonymous 

Do you mean you want to calculate the previous 4 weeks average by Name? If so try this: 

1. create the weeknumber column:

Weeknumber = WEEKNUM('Table'[Week Start])

2. create the result column 

Column =

var averagehour = CALCULATE(AVERAGE('Table'[Hours]),ALLEXCEPT('Table','Table'[Name]),'Table'[Weeknumber]>=EARLIER('Table'[Weeknumber])-4)

return IF([Hours]=0,averagehour)

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajulshah]

Hello @Anonymous,

Please create a calculated column as below:

Index = RANKX(WeekAverage,WeekAverage[Week Start],FIRSTDATE(WeekAverage[Week Start]),ASC,Dense)

And create a calculated column as below:

FinalHours = 
VAR Hours = SUM(WeekAverage[Hours])
VAR SelectedIndex = WeekAverage[Index]-4
VAR AveragePrevWeeks = CALCULATE(SUM(WeekAverage[Hours]),FILTER(WeekAverage,WeekAverage[Index]>=SelectedIndex))
VAR AverageWeek = IF(WeekAverage[Hours]=0,DIVIDE(AveragePrevWeeks,4),WeekAverage[Hours])
RETURN AverageWeek

Please let me know if this is not what you expect.

[Anonymous]

Sorry i have't provided correct data. It actually worked for the previous scenario. But i have attached actual input file where we have multiple names and dates. when i used same calculation it is producing different values. could you please provide solution where we have repeating names and dates.

Below is the actual data:

Name	Week Start	Hours
A	02/10/2020	0
A	02/17/2020	0
A	02/24/2020	0
A	03/02/2020	0
A	03/09/2020	0
A	03/16/2020	0
A	03/23/2020	0
A	03/30/2020	0
A	04/06/2020	0
A	04/13/2020	0
B	04/20/2020	0
B	04/27/2020	0
B	05/04/2020	0
B	05/11/2020	0
B	05/18/2020	0
B	05/25/2020	0
B	06/01/2020	0
B	06/08/2020	0
B	06/15/2020	0
B	06/22/2020	0
B	06/29/2020	0
B	07/06/2020	0
C	07/13/2020	0
C	07/20/2020	0
C	07/27/2020	0
C	08/03/2020	0
C	08/10/2020	0