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% |
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
Dear Sir,
Thank you so much sir for helping me. but i don't know what i am doing wrong.
I am attaching my file, please help me to get Average Head count and attrition.
Your help can save my life.
Hi Hitesh,
No file attached. Please be sure to click 'start upload' after selecting your file.
I think this is a Power Pivot question, not Power Query.
Mynda
Sorry my mistake, attaching file again
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]
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.
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. |
