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.

Non Working Days, Column 'Calendar'[IsWorkingDay], include: Sundays, Public Holidays & other special days (depending on circumstances).

Case in point (from tables below),

>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 amount/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 amount/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

Table Name: Calendar

Date	DayOfWeek	IsWorkingDay	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	0
14-Mar-20	Saturday	Non Working	7	0
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]

Perhaps something like:

Measure =
  VAR __Date = MAX('Table'[Date])
  VAR __Table = 
    ADDCOLUMNS(
      FILTER(
        ALL('Table'),
        [Date] <= __Date && [IsWorkingIndicator] = 1
      ),
      "__WorkDays",COUNTROWS(FILTER('Table','Table'[Date] <= EARLIER([Date]))
    )
  VAR __Max = MAXX(__Table,[__WorkDays])
  VAR __Lookup = IF(__Max >= 20,20,__Max)
RETURN
  AVERAGEX(FILTER(__Table,[__WorkDays] <= __Max),[Paid])

WARNING: I did not test this code.

[VO]

Given there are 2 tables, Calendar & Job

'Table' refers to

[VO]

Can't get it to work. Error: a single value for column Date in the table Job cannot be determined

Rolling Avg 20 Working Days :=
VAR __Date =
    MAX ( Job[Date] )
VAR __Table =
    ADDCOLUMNS (
        FILTER (
            ALL ( 'Calendar' ),
            Job[Date] <= __Date
                && 'Calendar'[IsWorkingIndicator] = 1
        ),
        "__WorkDays", COUNTROWS ( FILTER ( 'Calendar', 'Calendar'[Date] <= EARLIER ( Job[Date] ) ) )
    )
VAR __Max =
    MAXX ( __Table, [__WorkDays] )
VAR __Lookup =
    IF ( __Max >= 20, 20, __Max )
RETURN
    AVERAGEX ( FILTER ( __Table, [__WorkDays] <= __Max ), Job[Paid] )

[Anonymous · Accepted solution]

Here's the clean code:

// 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

[VO]

@Anonymous  thank you. It has yielded the expected results. Highly appreciated.