YTD Rolling Average by Month

[Anonymous]

Hi all,

I am relatively new to PowerBI so still learning everyday! I am trying to create a rolling YTD Average Headcount for Turnover analysis.

Below is a sumarised table that shows monthly headcount and leavers.

What I would like to show is YTD;

• in January, we had 1360 employees
• in February, it should show (1360+1372)/2 = 1366
• in March, it should show (1360+1372+X)/3 =  XXXX

and so on for the rest of the year. PLEASE HELP!! I am really stuck and have tried the quick measure but it isn't working:

2017 Monthly Headcount rolling average = 
IF(
	ISFILTERED('2017 Date'[Month]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	VAR __LAST_DATE = ENDOFMONTH('2017 Date'[Month].[Date])
	VAR __DATE_PERIOD =
		DATESBETWEEN(
			'2017 Date'[Month].[Date],
			STARTOFMONTH(DATEADD(__LAST_DATE, 1, MONTH)),
			ENDOFMONTH(DATEADD(__LAST_DATE, 1, MONTH))
		)
	RETURN
		AVERAGEX(
			CALCULATETABLE(
				SUMMARIZE(
					VALUES('2017 Date'),
					'2017 Date'[Month].[Year],
					'2017 Date'[Month].[QuarterNo],
					'2017 Date'[Month].[Quarter],
					'2017 Date'[Month].[MonthNo],
					'2017 Date'[Month].[Month]
				),
				__DATE_PERIOD
			),
			CALCULATE(
				SUM('2017 Date'[2017 Monthly Headcount]),
				ALL('2017 Date'[Month].[Day])
			)
		)
)

I have then created a running total measure and plan on then creating a calculation that divides the above with the below to give a monthly turnover %.

Thanks in advance!!

[Phil_Seamark · Accepted solution]

HI @Anonymous

Here is a simple calculated measure that might be close

Measure = TOTALYTD(
                SUM('2017 Date'[2017 Monthly Headcount]),
                '2017 Date'[Month]
                ) 
                / MAX('2017 Date'[Month Number])

If you put it on a Table visual with your Month column you get the correct results.

[Anonymous]

Thank you so much @Phil_Seamark

I have one more question - I have slicers on my dashbaord which allow our Executives to filter the data for their Divisions/Groups but it seems this is no longer linked to teh Turnover visualisation - any ideas? Would this be because I sumarised the the table?

Thank you