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...
Ashish_Mathur
Super User
Super UserHi,
Share data in a format the can be pasted in an MS Excel file.
| 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
Ashish_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.