Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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;
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!!
Solved! Go to 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.
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.
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
User | Count |
---|---|
122 | |
76 | |
63 | |
51 | |
50 |
User | Count |
---|---|
175 | |
125 | |
60 | |
60 | |
58 |