Hello everyone,
I am currently developing a chart that need to calculate the rolling average of the counts of events in a monthly basis. The problem that I encountered is my boss would like to have the rolling average reset after a new year. Means if the period I selected is from Jul 2022 to Aug 2023, then the rolling average will calculate from Jul 2022 to Dec 2022 and then started a new calculation from Jan 2023 to Aug 2023 as an example. So the graph will be something look like below.
The period of time is based on the drop down selection from a slicer and like a bit dynamic.
I have tried to found out a solution to manage it if every month have a event happened, but it is not working probably, if the month doesn't have event. Likle below:
May I seeks yours help on this question? It will be great if can be resolved and here attached are an example of the data set.
Here are some data set for references and as I am not sure how to upload the pbix, so will be great if can let me know how I can upload it here then we can share a bit with yours.
Problem occured when I have the slicer to filter by the event type and you can see the blue curve is disconnected.
And this is my rolling average dax.
Rolling average for events =
IF(
SELECTEDVALUE('ConsolidateValue'[YearMonth]),
VAR __LAST_DATE = ENDOFMONTH('ConsolidateValue'[YearMonth].[Date])
VAR __START_DATE = STARTOFMONTH('ConsolidateValue'[YearMonth].[Date])
VAR COUNT_PRV_MTH = COUNTAX(FILTER(ConsolidateValue,ConsolidateValue[Year]=MIN(ConsolidateValue[Year])),ConsolidateValue[YearMonth])
VAR __DATE_PERIOD =
DATESBETWEEN(
'ConsolidateValue'[YearMonth].[Date], BLANK(),
ENDOFMONTH(DATEADD(__START_DATE,COUNT_PRV_MTH-1,MONTH))
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('ConsolidateValue'),
'ConsolidateValue'[YearMonth].[Year],
'ConsolidateValue'[YearMonth].[QuarterNo],
'ConsolidateValue'[YearMonth].[Quarter],
'ConsolidateValue'[YearMonth].[MonthNo],
'ConsolidateValue'[YearMonth].[Month]
),
__DATE_PERIOD
),
CALCULATE(
COUNTA(Testing_dataSet[Event Number]), ConsolidateValue[Year]=MIN((ConsolidateValue[Year]))
)
)
)
And from excel, the table for potting the chart will be like below.
| Event Year | Event Month | Event Counts | Rolling Average |
| 2022 | Aug | 1 | 1.00 |
| 2022 | Sep | 0 | 0.50 |
| 2022 | Oct | 1 | 0.67 |
| 2022 | Nov | 0 | 0.50 |
| 2022 | Dec | 0 | 0.40 |
| 2023 | Jan | 0 | 0.00 |
| 2023 | Feb | 0 | 0.00 |
| 2023 | Mar | 1 | 0.33 |
| 2023 | Apr | 1 | 0.50 |
| 2023 | May | 1 | 0.60 |
| 2023 | Jun | 0 | 0.50 |
| 2023 | Jul | 0 | 0.43 |
Here below are the dataset that can be used for simulate.
| Event Number | Event Type | Event Caused by | Event Start | Event End |
| 7511436 | Networking | N/A | 2023-07-31T03:38:00Z | 2023-08-02T20:44:00Z |
| 7443276 | Collboation | N/A | 2023-07-25T05:30:00Z | 2023-07-25T08:27:00Z |
| 7290211 | Global | N/A | 2023-07-10T23:59:00Z | 2023-07-11T01:38:00Z |
| 7275269 | Webinar | N/A | 2023-07-10T04:53:00Z | 2023-07-10T10:29:00Z |
| 7183890 | Voice | N/A | 2023-06-29T01:37:00Z | 2023-06-29T11:12:00Z |
| 7139745 | Vitrual | 1104007 | 2023-06-26T02:00:00Z | 2023-06-26T07:15:00Z |
| 7142709 | Global | N/A | 2023-06-25T22:33:00Z | 2023-06-26T04:01:00Z |
| 7142302 | Communication | 1143602 | 2023-06-25T01:35:00Z | 2023-06-26T07:30:00Z |
| 6975911 | Voice | N/A | 2023-06-07T22:53:00Z | 2023-06-09T11:30:00Z |
| 6963355 | Voice | N/A | 2023-06-07T02:29:00Z | 2023-06-07T13:46:00Z |
| 6936432 | Voice | N/A | 2023-06-04T22:24:00Z | 2023-06-05T02:18:00Z |
| 6897016 | Morning | N/A | 2023-06-01T06:32:00Z | 2023-06-01T14:12:00Z |
| 6886711 | Voice | N/A | 2023-05-31T10:52:00Z | 2023-07-07T13:55:00Z |
| 6884622 | Voice | N/A | 2023-05-31T02:00:00Z | 2023-06-08T02:22:00Z |
| 6884573 | Voice | N/A | 2023-05-31T01:35:00Z | 2023-06-02T03:02:00Z |
| 6714491 | Webinar | 992317 | 2023-05-15T06:34:00Z | 2023-05-15T13:53:00Z |
| 6691884 | Party | 1021088 | 2023-05-11T21:31:00Z | 2023-05-13T14:43:00Z |
| 6678908 | Communication | N/A | 2023-05-11T01:15:00Z | 2023-05-11T08:51:00Z |
| 6643511 | Global | 1037776 | 2023-05-08T16:58:00Z | 2023-05-09T02:54:00Z |
| 6548690 | Webinar | 809894 | 2023-04-28T02:41:00Z | 2023-04-28T04:36:00Z |
| 6492845 | Morning | 923544 | 2023-04-24T01:55:00Z | 2023-04-24T03:21:00Z |
| 6397855 | Voice | N/A | 2023-04-14T01:26:00Z | 2023-04-17T02:04:00Z |
| 6316322 | Interview | 918368 | 2023-04-06T01:27:00Z | 2023-04-06T08:42:00Z |
| 6122594 | Internet | N/A | 2023-03-20T02:02:00Z | 2023-03-20T04:03:00Z |
| 6068343 | Webinar | N/A | 2023-03-14T03:19:00Z | 2023-03-14T09:40:00Z |
| 6011563 | Game | N/A | 2023-03-09T03:41:00Z | 2023-03-09T07:43:00Z |
| 5876362 | In person | N/A | 2023-03-06T08:42:00Z | 2023-03-06T11:01:00Z |
| 5597976 | Networking | N/A | 2023-03-01T05:16:00Z | 2023-03-01T16:11:00Z |
| 5459941 | Communication | 720072 | 2023-02-20T22:18:00Z | 2023-02-21T00:48:00Z |
| 5366282 | Voice | N/A | 2023-02-10T01:25:00Z | 2023-02-10T02:50:00Z |
| 5355895 | EMEA | N/A | 2023-02-09T04:28:00Z | 2023-02-09T06:09:00Z |
| 5295347 | Tradeweb | N/A | 2023-02-02T15:35:00Z | 2023-02-03T16:06:00Z |
| 5034139 | Lunch | N/A | 2023-01-03T22:47:00Z | 2023-01-04T02:07:00Z |
| 4929247 | Vitrual | 592532 | 2022-12-19T03:38:00Z | 2022-12-19T06:40:00Z |
| 4791145 | Webinar | N/A | 2022-12-05T07:44:00Z | 2022-12-05T16:09:00Z |
| 4697318 | Game | N/A | 2022-11-22T06:13:00Z | 2022-11-25T12:34:00Z |
| 4638964 | Multiple Combination | 587541 | 2022-11-16T05:47:00Z | 2022-11-16T07:45:00Z |
| 4625596 | Global | 592789 | 2022-11-15T02:14:00Z | 2022-11-15T03:51:00Z |
| 4520392 | Finance | N/A | 2022-11-07T04:08:00Z | 2022-11-07T08:01:00Z |
| 4556688 | Beach | 641893 | 2022-11-02T09:25:00Z | 2022-11-04T14:36:00Z |
| 4481040 | Internet | 637255 | 2022-10-28T02:00:00Z | 2022-10-28T06:48:00Z |
| 4458501 | Webinar | N/A | 2022-10-26T01:16:00Z | 2022-10-26T04:33:00Z |
| 4447627 | Business | N/A | 2022-10-24T23:45:00Z | 2022-10-26T04:31:00Z |
| 4436019 | Webinar | N/A | 2022-10-24T04:40:00Z | 2022-10-24T06:18:00Z |
| 4435733 | Internet | N/A | 2022-10-24T02:52:00Z | 2022-10-27T16:17:00Z |
| 4379826 | Boat Trip | N/A | 2022-10-17T21:51:00Z | 2022-10-18T07:00:00Z |
| 4367838 | Webinar | N/A | 2022-10-17T03:40:00Z | 2022-10-17T10:42:00Z |
| 4367782 | Lunch | 609224 | 2022-10-17T03:14:00Z | 2022-10-18T14:05:00Z |
| 4345744 | Dinner | 415991 | 2022-10-14T02:21:00Z | 2022-10-14T07:32:00Z |
| 4216422 | Unclassify | N/A | 2022-09-29T00:00:00Z | 2022-09-29T04:02:00Z |
| 4048593 | Voice | 525436 | 2022-09-19T08:19:00Z | 2022-09-19T09:52:00Z |
| 4124467 | Voice | N/A | 2022-09-09T07:04:00Z | 2022-09-12T23:02:00Z |
| 3981790 | EMEA | N/A | 2022-08-31T05:23:00Z | 2022-08-31T08:08:00Z |
| 3938017 | Webinar | N/A | 2022-08-25T14:06:00Z | 2022-08-31T17:08:00Z |
| 3925912 | Semiar | N/A | 2022-08-24T07:32:00Z | 2022-08-25T02:48:00Z |
| 3904803 | Asia | 491658 | 2022-08-22T15:41:00Z | 2022-08-22T22:01:00Z |
| 3744329 | Webinar | N/A | 2022-08-04T06:33:00Z | 2022-08-04T14:53:00Z |
| 3709189 | Global | N/A | 2022-08-01T00:56:00Z | 2022-08-01T03:41:00Z |
| 3623790 | Webinar | N/A | 2022-07-21T02:56:00Z | 2022-07-21T04:23:00Z |
| 3591738 | Webinar | N/A | 2022-07-18T02:55:00Z | 2022-07-18T11:14:00Z |
| 3487958 | All | N/A | 2022-07-06T01:28:00Z | 2022-07-06T06:46:00Z |
Thank you so much!
Helpful resources
New to Fabric Survey
If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.
Power BI DataViz World Championships - June 2026
A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.
Join our Fabric User Panel
Share feedback directly with Fabric product managers, participate in targeted research studies and influence the Fabric roadmap.
Power BI Community Update - March 2026
Check out the March 2026 Power BI update to learn about new features.
