Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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.
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.
@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.
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.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |