Rolling Average Last 20 Working Days (Exclude Non Working Days)

[VO]

Fact table, Job. Calendar table, Calendar. Would like to get the 20 working day rolling average (days with zero, no activity, included as long as it is a working day),  when it gets to 20 working days or more transactions.

For less than 20 working days (initial range at the beginning before 20 working days), average the cumulative working days.

Case in point,

10-Mar-2020: Rolling 20 day Average= 57.5 [8 working days]
30-Mar-2020: Rolling 20 day Average= 80.85 [20 working days] (inclusive 23-Mar-2020 & 24-Mar-2020, no activity despite it being a working day)
31-Mar-2020: Rolling 20 day Average= 79.85 [20 working days] (inclusive 23-Mar-2020 & 24-Mar-2020, no activity despite it being a working day)

Table Name: Job

Date	Paid
02-Mar-20	20
03-Mar-20	30
04-Mar-20	40
05-Mar-20	50
06-Mar-20	60
07-Mar-20	70
09-Mar-20	90
10-Mar-20	100
11-Mar-20	110
12-Mar-20	120
16-Mar-20	102
17-Mar-20	62
18-Mar-20	101
19-Mar-20	94
20-Mar-20	136
21-Mar-20	119
25-Mar-20	125
26-Mar-20	43
27-Mar-20	144
28-Mar-20	39
30-Mar-20	52
31-Mar-20	30

Table Name: Calendar

Date	DayOfWeek	WorkingDay	WeekDayNumber	IsWorkingIndicator
01-Mar-20	Sunday	Non Working	1	0
02-Mar-20	Monday	Working	2	1
03-Mar-20	Tuesday	Working	3	1
04-Mar-20	Wednesday	Working	4	1
05-Mar-20	Thursday	Working	5	1
06-Mar-20	Friday	Working	6	1
07-Mar-20	Saturday	Working	7	1
08-Mar-20	Sunday	Non Working	1	0
09-Mar-20	Monday	Working	2	1
10-Mar-20	Tuesday	Working	3	1
11-Mar-20	Wednesday	Working	4	1
12-Mar-20	Thursday	Working	5	1
13-Mar-20	Friday	Non Working	6	1
14-Mar-20	Saturday	Non Working	7	1
15-Mar-20	Sunday	Non Working	1	0
16-Mar-20	Monday	Working	2	1
17-Mar-20	Tuesday	Working	3	1
18-Mar-20	Wednesday	Working	4	1
19-Mar-20	Thursday	Working	5	1
20-Mar-20	Friday	Working	6	1
21-Mar-20	Saturday	Working	7	1
22-Mar-20	Sunday	Non Working	1	0
23-Mar-20	Monday	Working	2	1
24-Mar-20	Tuesday	Working	3	1
25-Mar-20	Wednesday	Working	4	1
26-Mar-20	Thursday	Working	5	1
27-Mar-20	Friday	Working	6	1
28-Mar-20	Saturday	Working	7	1
29-Mar-20	Sunday	Non Working	1	0
30-Mar-20	Monday	Working	2	1
31-Mar-20	Tuesday	Working	3	1

[Greg_Deckler]

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/td-p/367362 This should provide some of the solution.

[VO]

Thank you for the suggested solution. In addition to Sunday as a Non Working day. There will also be additional days excluded, that is, Non Working (public holidays and special days). The 'Calendar'[IsWorkingDay] column would capture the various scenarios. Hence, I am not sure the proposed solution will cover all the scenarios highlighted.

[Anonymous · Accepted solution]

// 20 [W]orking [D]ay Rolling Avg
[20WD Rolling Avg] =
var __workingDays = 20
var __currentDay = MAX( 'Calendar'[Date] )
var __daysToAvgOver =
	TOPN(__workingDays,
		CALCULATETABLE(
			'Calendar',
			'Calendar'[Date] <= __currentDay,
			'Calendar'[IsWorkingIndicator] = 1,
			ALL( 'Calendar' )
		),
		'Calendar'[Date],
		DESC
	)
var __avg =
	DIVIDE(
		CALCULATE(
			SUM( Job[Paid] ),
			__daysToAvgOver
		),
		COUNTROWS( __daysToAvgOver )
	)
return
	__avg

Best

D