Forum Discussion

VO's avatar
VO
Icon for Helper I rankHelper I
6 years ago
Solved

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

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

DatePaid
02-Mar-2020
03-Mar-2030
04-Mar-2040
05-Mar-2050
06-Mar-2060
07-Mar-2070
09-Mar-2090
10-Mar-20100
11-Mar-20110
12-Mar-20120
16-Mar-20102
17-Mar-2062
18-Mar-20101
19-Mar-2094
20-Mar-20136
21-Mar-20119
25-Mar-20125
26-Mar-2043
27-Mar-20144
28-Mar-2039
30-Mar-2052
31-Mar-2030

 

Table Name: Calendar

DateDayOfWeekWorkingDayWeekDayNumberIsWorkingIndicator
01-Mar-20SundayNon Working10
02-Mar-20MondayWorking21
03-Mar-20TuesdayWorking31
04-Mar-20WednesdayWorking41
05-Mar-20ThursdayWorking51
06-Mar-20FridayWorking61
07-Mar-20SaturdayWorking71
08-Mar-20SundayNon Working10
09-Mar-20MondayWorking21
10-Mar-20TuesdayWorking31
11-Mar-20WednesdayWorking41
12-Mar-20ThursdayWorking51
13-Mar-20FridayNon Working61
14-Mar-20SaturdayNon Working71
15-Mar-20SundayNon Working10
16-Mar-20MondayWorking21
17-Mar-20TuesdayWorking31
18-Mar-20WednesdayWorking41
19-Mar-20ThursdayWorking51
20-Mar-20FridayWorking61
21-Mar-20SaturdayWorking71
22-Mar-20SundayNon Working10
23-Mar-20MondayWorking21
24-Mar-20TuesdayWorking31
25-Mar-20WednesdayWorking41
26-Mar-20ThursdayWorking51
27-Mar-20FridayWorking61
28-Mar-20SaturdayWorking71
29-Mar-20SundayNon Working10
30-Mar-20MondayWorking21
31-Mar-20TuesdayWorking31
  • Anonymous's avatar
    Anonymous
    6 years ago

     

     

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

3 Replies

    • VO's avatar
      VO
      Icon for Helper I rankHelper I
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

         

         

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