Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

YTD Rolling Average by Month

 

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.

 

Headcount by Month.PNG

 

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 %.

 

Running Leavers.PNG

Thanks in advance!!

1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee

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.

 

image.png

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Phil_Seamark
Employee
Employee

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.

 

image.png

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

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?

 

Turnover by month.PNG

 

 

Thank you

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors