Calculate rolling average with daily values information

[calvgo]

I am trying to get the rolling 3 and/or 12 months average from the below sample data.

For this example, the average for each month is as follows in the pivot table:

Mar	26
Apr	8
May	33
Jun	47
Jul	91
Aug	61
Sep	59
Oct	146
Nov	118

Therefore, I will get the three month rolling average for May = (26+8+33)/3 = 22; Nov = (59+146+118)/3 = 108 and so forth. 

I like to be able to adjust and calculate the rolling average to 12 months as well. 

Thanks. 

Sample Data

Closed Date	Days taken
4/03/2021 0:00	14
4/03/2021 0:00	13
10/03/2021 0:00	0
11/03/2021 0:00	65
11/03/2021 0:00	14
15/03/2021 0:00	17
22/03/2021 0:00	32
26/03/2021 0:00	52
1/04/2021 0:00	8
6/04/2021 0:00	36
7/04/2021 0:00	0
14/04/2021 0:00	0
20/04/2021 0:00	0
22/04/2021 0:00	0
23/04/2021 0:00	3
23/04/2021 0:00	0
27/04/2021 0:00	4
27/04/2021 0:00	0
29/04/2021 0:00	45
29/04/2021 0:00	0
3/05/2021 0:00	96
3/05/2021 0:00	0
6/05/2021 0:00	24
6/05/2021 0:00	9
10/05/2021 0:00	3
11/05/2021 0:00	0
12/05/2021 0:00	90
13/05/2021 0:00	16
17/05/2021 0:00	7
18/05/2021 0:00	111
18/05/2021 0:00	84
18/05/2021 0:00	7
27/05/2021 0:00	20
28/05/2021 0:00	0
1/06/2021 0:00	56
1/06/2021 0:00	13
2/06/2021 0:00	55
2/06/2021 0:00	43
2/06/2021 0:00	21
3/06/2021 0:00	13
7/06/2021 0:00	131
7/06/2021 0:00	87
7/06/2021 0:00	20
7/06/2021 0:00	0
8/06/2021 0:00	39
9/06/2021 0:00	261
9/06/2021 0:00	61
9/06/2021 0:00	56
9/06/2021 0:00	30
9/06/2021 0:00	21
9/06/2021 0:00	6
11/06/2021 0:00	0
17/06/2021 0:00	14
18/06/2021 0:00	65
18/06/2021 0:00	31
18/06/2021 0:00	25
22/06/2021 0:00	39
24/06/2021 0:00	37
29/06/2021 0:00	56
5/07/2021 0:00	108
5/07/2021 0:00	140
6/07/2021 0:00	4
15/07/2021 0:00	70
16/07/2021 0:00	59
16/07/2021 0:00	28
16/07/2021 0:00	17
16/07/2021 0:00	0
19/07/2021 0:00	90
22/07/2021 0:00	125
22/07/2021 0:00	87
22/07/2021 0:00	49
23/07/2021 0:00	154
23/07/2021 0:00	80
23/07/2021 0:00	78
26/07/2021 0:00	153
26/07/2021 0:00	136
26/07/2021 0:00	53
27/07/2021 0:00	166
27/07/2021 0:00	103
29/07/2021 0:00	125
30/07/2021 0:00	172
3/08/2021 0:00	156
5/08/2021 0:00	100
5/08/2021 0:00	30
10/08/2021 0:00	82
10/08/2021 0:00	0
11/08/2021 0:00	7
12/08/2021 0:00	0
13/08/2021 0:00	85
13/08/2021 0:00	28
19/08/2021 0:00	56
20/08/2021 0:00	144
24/08/2021 0:00	77
24/08/2021 0:00	0
25/08/2021 0:00	14
30/08/2021 0:00	54
30/08/2021 0:00	10
31/08/2021 0:00	165
31/08/2021 0:00	84
1/09/2021 0:00	141
1/09/2021 0:00	82
1/09/2021 0:00	43
1/09/2021 0:00	26
2/09/2021 0:00	0
3/09/2021 0:00	45
6/09/2021 0:00	144
7/09/2021 0:00	0
9/09/2021 0:00	0
10/09/2021 0:00	85
14/09/2021 0:00	6
17/09/2021 0:00	0
21/09/2021 0:00	109
22/09/2021 0:00	106
24/09/2021 0:00	28
24/09/2021 0:00	0
27/09/2021 0:00	152
27/09/2021 0:00	0
28/09/2021 0:00	138
29/09/2021 0:00	84
5/10/2021 0:00	162
5/10/2021 0:00	147
5/10/2021 0:00	116
5/10/2021 0:00	0
8/10/2021 0:00	14
15/10/2021 0:00	148
15/10/2021 0:00	260
15/10/2021 0:00	106
15/10/2021 0:00	101
15/10/2021 0:00	414
21/10/2021 0:00	191
21/10/2021 0:00	175
21/10/2021 0:00	141
21/10/2021 0:00	63
2/11/2021 0:00	82
2/11/2021 0:00	42
8/11/2021 0:00	181
8/11/2021 0:00	172
8/11/2021 0:00	153
8/11/2021 0:00	97
9/11/2021 0:00	97

[v-angzheng-msft · Accepted solution]

Hi, @calvgo 

Try to create measures like this:

Rolling 3 month = 
DIVIDE(
CALCULATE(
    SUM('Table'[Days taken]),
    FILTER(ALL('Table'),
    EOMONTH('Table'[Closed Date],0)<=EOMONTH(MAX('Table'[Closed Date]),0)&&EOMONTH('Table'[Closed Date],0)>EOMONTH(MAX('Table'[Closed Date]),-3)
    )),
    3)

Rolling 12 month = 
DIVIDE(
CALCULATE(
    SUM('Table'[Days taken]),
    FILTER(ALL('Table'),
    EOMONTH('Table'[Closed Date],0)<=EOMONTH(MAX('Table'[Closed Date]),0)&&EOMONTH('Table'[Closed Date],0)>EOMONTH(MAX('Table'[Closed Date]),-12)
    )),
    12)

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[amitchandak]

@calvgo , if you need avg of sum, with help from date table

Rolling 3 = calculate(AverageX(Values('Date'[MONTH Year]),CALCULATE(sum(Table[Days taken])),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

Rolling 12 = calculate(AverageX(Values('Date'[MONTH Year]),CALCULATE(sum(Table[Days taken])),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

You need simple avg

Rolling 12 = CALCULATE(Average(Table[Days taken),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[calvgo]

Hi @amitchandak 

For some reason the calculation did not work properly based on your calculations. There are two parts to the calc. Can you show how you 1) perform the average for each month for 'days taken', and then 2) perform the 3/12 months rolling average from the part 1) monthly average? Can you load up the sample data and show the process please. 

Appreciate it.