

August 26, 2019

Hello,
I am new in This group, thanks in advance. Any one please help me to how to count Average head count. That can help me lot.
Heading | Discretion | FX Formula |
JoinersCounts | Per month Joiner | CALCULATE(COUNTROWS(Joiners)) |
leaversCounts | Per month Leavers | CALCULATE(COUNTROWS(Leavers)) |
Active Head Count | Net Change | CALCULATE([JoinersCounts]-[leaversCounts], FILTER(ALLSELECTED ('Date'[Date]), ISONORAFTER ('Date'[Date], MAX('Date'[Date]), DESC))) |
YTD_Joiners | Year star date till month JoinersCounts | CALCULATE([JoinersCounts], DATESYTD('Date'[Date],"31/12")) |
YTD_Leavers | Year star date till month leaversCounts | CALCULATE([leaversCounts], DATESYTD('Date'[Date],"31/12")) |
YTD Avg HC | Avg of Year till of Active Active Head Count | Can you please how to count Average of Active Head count |
Month | numric Month No | No of month / Quarter |
Monthly Attrition | (YTD_Leavers/YTD Avg HC*12)/Month | Can you please how to count Monthly Attrition |
Date (Quarter) | Date (Month) | Joiners Counts | leavers Counts | Active Head Count | YTD Joiners | YTD Leavers | Avg HC | Month | Monthly Attrition |
Qtr1 | Jan | 10 | 5 | 25 | 10 | 5 | 25 | 1 | 240.0% |
Feb | 15 | 7 | 33 | 25 | 12 | 29 | 2 | 248.3% | |
Mar | 20 | 8 | 45 | 45 | 20 | 34 | 3 | 233.0% | |
Qtr1 Total | 45 | 20 | 45 | 45 | 20 | 34 | 3 | 235.3% | |
Qtr2 | Apr | 15 | 4 | 56 | 60 | 24 | 51 | 4 | 142.6% |
May | 20 | 5 | 71 | 80 | 29 | 57 | 5 | 121.4% | |
Jun | 25 | 9 | 87 | 105 | 38 | 65 | 6 | 117.4% |


November 8, 2013

Hi Hitesh,
RunningSum = SUMX(FILTER(ALLSELECTED('Date'[Date]),'Date'[Date]<=MAX('Date'[Date])),[Active Head Count])
A month Count is needed:
MonthCount =
VAR _start = STARTOFYEAR( 'Calendar'[Date] ) -1
VAR _end = ENDOFMONTH( 'Calendar'[Date] )
RETURN DATEDIFF( _start, _end, MONTH )
(you can use though the Month column you have)
YTD Avg HC=DIVIDE(RunningSum, MonthCount)
Monthly Attrition = ([YTD_Leavers]/[YTD Avg HC]*12)/MonthCount

Answers Post


November 8, 2013

Hi Hitesh,
As mentioned, for the Average you need a month count:
MonthCount:=VAR _start = STARTOFYEAR( ALL('Date'[Date] ))
VAR _end = ENDOFMONTH( 'Date'[Date] )
RETURN DATEDIFF( _start, _end, MONTH )+1
Then, the YTD Avg HC is very simple:
YTD Avg HC:=DIVIDE([Active Head Count], [MonthCount])
Monthly Attrition is basically your formula:
Monthly Attrition:=([YTD_Leavers]/[YTD Avg HC]*12)/[MonthCount]


August 26, 2019

Dear Sir,
Please help me for above topic, if you have not understand my question than please let me know. i have put excel formula how i want answer, i don't know how to get answer in Power pivot.
Sorry to disturb you but this problem can solve lots of my issue.
Again Thank you so much in advance.


August 26, 2019

Dear Team,
Please help me to find Average of Active Head count using measures.
Active Head Count := | CALCULATE( [JoinersCounts]- [leaversCounts],FILTER (ALLSELECTED ('Date'[Date]), ISONORAFTER ('Date'[Date], MAX('Date'[Date]), DESC))) |
Avg. of Active Head Count | Please help me to find Avg. of Active Head Count for each month example given below. If possible please do calculation in attached excel only. |
1 Guest(s)
