Forum Discussion
DAX
- 3 years ago
First you need to calculate Active Headcounts for each month/date. That means if person left/hire during that time, he must be included/excluded from active head counts. There should be "Leaving Date" , "Hiring Date" in your data.
Create a YTD measure of based on above.
Create a measure that will calculate exit only.
The architecture will include 1-active relationship between Date and Employee table. Lets say it is connected with Hiring Date.
Then there will be a inactive relationship between Date and Leaving date to calculate exits based on this relationship.
In below example there is a Column active and it will hold "1" for all the employees and "Left" column where employees left or Leaving date is not null
_Hire = CALCULATE(SUM(Employee[Active]),USERELATIONSHIP(PBI_DimDate[DateSecAlternateKey],Employee[HiringDate]))_Hire YTD = CALCULATE([_Hire],DATESYTD(PBI_DimDate[DateSecAlternateKey]))_Left = CALCULATE(SUM(Employee[Left]),USERELATIONSHIP(PBI_DimDate[DateSecAlternateKey],Employee[LeavingDate]))_ActiveAll = Var dt = Max(PBI_DimDate[DateSecAlternateKey]) RETURN CALCULATE(COUNTROWS(Employee),FILTER(ALLEXCEPT(Employee,Employee[CountryCode]),(Employee[HiringDate]<=dt && (Employee[LeavingDate]>=dt || ISBLANK(Employee[LeavingDate])=TRUE()) )))Then create your above formula using above measures..
Annualized Attrition = (Total YTD Exits/YTD Average Headcount) * (12/Number of months passed)
First you need to calculate Active Headcounts for each month/date. That means if person left/hire during that time, he must be included/excluded from active head counts. There should be "Leaving Date" , "Hiring Date" in your data.
Create a YTD measure of based on above.
Create a measure that will calculate exit only.
The architecture will include 1-active relationship between Date and Employee table. Lets say it is connected with Hiring Date.
Then there will be a inactive relationship between Date and Leaving date to calculate exits based on this relationship.
In below example there is a Column active and it will hold "1" for all the employees and "Left" column where employees left or Leaving date is not null
_Hire = CALCULATE(SUM(Employee[Active]),USERELATIONSHIP(PBI_DimDate[DateSecAlternateKey],Employee[HiringDate]))_Hire YTD = CALCULATE([_Hire],DATESYTD(PBI_DimDate[DateSecAlternateKey]))_Left = CALCULATE(SUM(Employee[Left]),USERELATIONSHIP(PBI_DimDate[DateSecAlternateKey],Employee[LeavingDate]))
_ActiveAll =
Var dt = Max(PBI_DimDate[DateSecAlternateKey])
RETURN
CALCULATE(COUNTROWS(Employee),FILTER(ALLEXCEPT(Employee,Employee[CountryCode]),(Employee[HiringDate]<=dt && (Employee[LeavingDate]>=dt || ISBLANK(Employee[LeavingDate])=TRUE()) )))
Then create your above formula using above measures..
Annualized Attrition = (Total YTD Exits/YTD Average Headcount) * (12/Number of months passed)