topic Re: Rolling Average Last 20 Working Days (Exclude Non Working Days) in DAX Commands and Tips

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

VO — Tue, 24 Mar 2020 20:20:30 GMT

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

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

Greg_Deckler — Tue, 24 Mar 2020 20:48:27 GMT

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.

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

VO — Tue, 24 Mar 2020 21:14:23 GMT

Given there are 2 tables, Calendar & Job

'Table' refers to

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

VO — Tue, 24 Mar 2020 21:34:41 GMT

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] )

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

Anonymous — Tue, 24 Mar 2020 21:37:31 GMT

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

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

VO — Tue, 24 Mar 2020 21:51:51 GMT

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