Forum Discussion
12 Months rolling average
Hello Community, I hav a table (Case) with : 1. Case ID 2. Casedatevalue which contains date and is of date type I want to have count of case id by month and also rolling average for 12 mon...
| CaseId | CaseDateValue |
| 22263 | 12/24/2022 0:00 |
| 22329 | 2/13/2023 0:00 |
| 22359 | 12/5/2022 0:00 |
| 72568 | 12/31/2022 0:00 |
| 77068 | 12/21/2022 0:00 |
| 78229 | 12/6/2022 0:00 |
| 78845 | 12/7/2022 0:00 |
| 78865 | 12/12/2022 0:00 |
| 81065 | 12/8/2022 0:00 |
| 82779 | 3/1/2023 0:00 |
| 82790 | 1/1/2023 0:00 |
| 85145 | 1/30/2023 0:00 |
| 87673 | 1/20/2023 0:00 |
| 90930 | 1/1/2023 0:00 |
| 91137 | 6/1/2023 0:00 |
| 93634 | 1/30/2023 0:00 |
| 93785 | 1/1/2023 0:00 |
| 94605 | 1/1/2023 0:00 |
| 95672 | 12/8/2022 0:00 |
| 96195 | 12/21/2022 8:31 |
| 96284 | 1/1/2023 0:00 |
| 96412 | 1/1/2023 0:00 |
| 96416 | 1/1/2023 0:00 |
| 96421 | 1/1/2023 0:00 |
| 96422 | 1/1/2023 0:00 |
| 96423 | 1/1/2023 0:00 |
| 96424 | 1/1/2023 0:00 |
| 96426 | 1/1/2023 0:00 |
| 96427 | 1/1/2023 0:00 |
| 96429 | 1/1/2023 0:00 |
| 96431 | 1/1/2023 0:00 |
| 96432 | 1/1/2023 0:00 |
| 96434 | 1/1/2023 0:00 |
| 96435 | 1/1/2023 0:00 |
| 96436 | 1/1/2023 0:00 |
| 96437 | 1/1/2023 0:00 |
| 96842 | 1/1/2023 0:00 |
| 96847 | 12/27/2022 10:34 |
| 96875 | 1/1/2023 0:00 |
| 96990 | 1/1/2023 0:00 |
| 97096 | 12/1/2022 0:38 |
| 97099 | 12/1/2022 0:00 |
| 97104 | 12/1/2022 11:59 |
| 97106 | 12/1/2022 0:00 |
| 97109 | 12/1/2022 12:51 |
| 97128 | 12/1/2022 0:00 |
| 97146 | 12/1/2022 13:00 |
| 97152 | 12/1/2022 12:12 |
| 97158 | 12/1/2022 18:54 |
| 97199 | 12/1/2022 0:00 |
| 97200 | 12/1/2022 0:00 |
| 97201 | 12/1/2022 0:00 |
| 97202 | 12/2/2022 0:00 |
| 97204 | 12/2/2022 10:08 |
| 97214 | 12/2/2022 8:01 |
| 97251 | 12/2/2022 11:50 |
| 97252 | 12/2/2022 17:00 |
| 97257 | 12/2/2022 0:00 |
| 97266 | 12/2/2022 0:00 |
| 97269 | 12/2/2022 14:09 |
| 97270 | 12/2/2022 0:00 |
| 97271 | 12/2/2022 14:21 |
| 97280 | 12/2/2022 15:45 |
| 97283 | 12/2/2022 0:00 |
| 97287 | 12/1/2022 0:00 |
| 97308 | 12/2/2022 0:00 |
| 97322 | 12/4/2022 22:58 |
| 97328 | 12/2/2022 11:00 |
| 97331 | 12/5/2022 0:00 |
| 97339 | 12/12/2022 0:00 |
| 97341 | 12/19/2022 0:00 |
| 97342 | 12/1/2022 0:00 |
| 97354 | 12/5/2022 15:28 |
| 97357 | 12/5/2022 15:32 |
| 97360 | 12/1/2022 0:00 |
| 97381 | 12/5/2022 0:00 |
| 97403 | 12/1/2022 0:00 |
| 97409 | 12/5/2022 0:00 |
| 97415 | 12/1/2022 15:38 |
| 97416 | 12/5/2022 0:00 |
| 97417 | 12/5/2022 0:00 |
| 97418 | 12/5/2022 0:00 |
| 97422 | 12/5/2022 16:58 |
| 97423 | 12/5/2022 17:05 |
| 97424 | 12/5/2022 0:00 |
| 97433 | 12/5/2022 0:00 |
| 97436 | 12/6/2022 6:41 |
| 97437 | 12/6/2022 6:44 |
| 97438 | 12/2/2022 0:00 |
| 97439 | 12/2/2022 0:00 |
| 97440 | 12/2/2022 0:00 |
| 97443 | 12/1/2022 7:02 |
| 97444 | 12/1/2022 7:05 |
| 97445 | 12/6/2022 11:35 |
| 97447 | 12/1/2022 0:00 |
| 97448 | 12/1/2022 7:16 |
| 97449 | 12/6/2022 7:20 |
| 97451 | 12/6/2022 7:34 |
| 97456 | 12/3/2022 0:00 |
| 97464 | 12/6/2022 0:00 |
Ashish_Mathur
Super User
Super UserAshish_Mathur Thank you so much for your solution and prompt response.
Just want to understand some points:
1. "ABCD",[Case count]),[ABCD]) . Couls you Please explain what is "ABCD" as it's not table or column or measure in the file.2. As discussed, as its is showing rolling average correctly as expected. As Discussed, if I select date as slicer and select 2023 or 2022, then will it give me last 12 month rolling average from the current month of 2023?- Ashish_Mathur
You are welcome. ABCD is the title of the virtual column. You may give any name instead of ABCD. Glad that the formula is working fine.
Ashish_Mathur The average are correct. But as it is 12 month rolling average for example Feb 2023, it should go back 11 month adds the counts from March 2022 to Feb 2023 (62) and divide by 12 which will be 5.16666.
As per your explanation it is doing rolling average but not considering 12 month rolling average for example for Feb 2023 it is summing the count from Jan 2022 to feb 2023.