Re: Rolling Average across the years with blank mo...

Anonymous · ‎08-07-2023

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!

Anonymous · ‎08-15-2023

Will be great if someone can help on the above? Sorry about my question...

Anonymous · ‎08-09-2023

Not sure does anyone have some sort of idea? Maybe that is a bit wired approach, but running out of ideas. Sorry.

mussaenda · ‎08-07-2023

Hi @Anonymous ,

Try changing your x axis to continuous instead of Categorical, the line will be connected.

Another solution is to add +0 on your formula for the blue line so it will be connected.

Hope this helps

Anonymous · ‎08-07-2023

Hello Mussaenda,

Thanks for yours suggestion, I have already enabled to continous option. However, the problem I think is the calculation is not correct if the rolling average is across two years which will not be reset to a new rolling average after a new year in the selection.