Hi Team
Please help me out calculate Average Head count & Attrition in Power Query in excel. File Attached.
| 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 |
| 12 month Hires | 12 month Hires | CALCULATE([Hires],DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH)) |
| 12 month Exits | 12 month Exits | CALCULATE([Exits],DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH)) |
| 12 month Avg HC | Avg of last 12 month Active Active Head Count | Can you please how to count 12 month 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 |
