HR Analytics - Active Employee, Hire and Termination trend
Human resources software is one of the most common modules used by any organization. While it is simple to understand, it contains a few challenging analytical calculations. One such calculation is Active employees. In this article, we will not only discuss how can we get that, but we will also showcase hire and termination calculation on the same Date table. One more calculation which we cover here: One measure to provide last period calculation for both month and year.
DAX measure for calculating Employee exits
I have the following 4 tables (there are more tables and columns, but these are the ones that I am concerned with for my calculation) and their respective columns: 1) Date_table (Date, Year, Quarter, Month, Day) 2) Employee_table (Emp_Id, StartDate, EndDate, Termination, Country, Wok_Id) 3) PositWorker_table (Wok_Id, JobType, Department) 4) Worker_table (Wok_Id, Gender, EmploymentCategory) The following relationships exist between these tables: 1) Employee_table(EndDate) links with Date_table(Date) [Single direction (cross filter), Many:1 relationship] 2) Worker_table(Wok_Id) links with PositWorker_table(Wok_Id) [Single direction (cross filter), Many:1 relationship] 3) Employee_table(Wok_Id) links with Worker_table(Wok_Id) [Single direction (cross filter), Many:1 relationship] 4) Employee_table(Wok_Id) and PositWorker_table(Wok_Id) [Single direction (cross filter), Many:1 relationship] The following filters are being used on a page level: 1) Employee_table(Termination) is not equal to "Transfer" (there are 5 other options) 2) Worker_table(EmploymentCategory) is not equal to "Intern" or "Working Student" (there are 4 other options) 3) PositWorker_table(JobType) is not equal to "Intern", "Student" or "Blank" (there are 8 other options) 4) PositWorker_table(Department) is not equal to AAD (there are 30 more options) I am using the following measure: Exits = VAR exits = CALCULATE ( COUNT ( Employee_table[Emp_Id] ), USERELATIONSHIP ( Employee_table[EndDate], Date_table[Date] ) , CROSSFILTER(Worker_table[Wok_Id],PositWorker_table[Wok_Id], Both) ) RETURN IF ( ISBLANK ( exits ), 0 , exits ) The results being generated are slightly higher than what should be coming. What changes need to be made in the measure or relationships of the data model so that I get the right answer?
