Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Floks,
I'm trying to build Employee Attriation Dashboard. I have following SharePoint list structure where we are managing employee details:
Employee ID | Employee Name | Designation | Department | Location | Hire Date | Termination Date |
E101 | Emp Name 1 | Dept A | US | 1/1/20012 | ||
E102 | Emp Name 2 | Dept B | India | 5/1/2013 | ||
E103 | Emp Name 3 | Dept C | Germany | 5/5/2014 | 2/4/2015 | |
E104 | Emp Name 4 | Dept A | UK | 8/2/2014 | ||
E105 | Emp Name 5 | Dept D | US | 8/5/2014 | ||
E106 | Emp Name 6 | Dept B | India | 8/8/2014 | 5/12/2015 | |
E107 | Emp Name 7 | Dept C | Germany | 9/12/2014 | ||
E108 | Emp Name 8 | Dept A | UK | 3/3/2015 | ||
E109 | Emp Name 9 | Dept B | US | 4/18/2015 | ||
E110 | Emp Name 10 | Dept D | India | 6/8/2015 | ||
E111 | Emp Name 11 | Dept E | Germany | 8/8/2015 | 5/5/2016 | |
E112 | Emp Name 12 | Dept B | UK | 9/9/2015 | ||
E113 | Emp Name 13 | Dept C | US | 1/10/2016 | ||
E114 | Emp Name 14 | Dept C | India | 4/18/2016 | 2/8/2017 | |
E115 | Emp Name 15 | Dept B | Germany | 8/15/2016 | ||
E116 | Emp Name 16 | Dept A | UK | 12/5/2016 | ||
E117 | Emp Name 17 | Dept D | US | 1/9/2017 | ||
E118 | Emp Name 18 | Dept B | India | 1/20/2017 | ||
E119 | Emp Name 19 | Dept B | Germany | 2/2/2017 | ||
E120 | Emp Name 20 | Dept C | UK | 2/14/2017 |
I tried to create measure of Emp Head count at the time of starting of respective period.
Total Employee = CALCULATE (COUNTROWS( Employee ), FILTER ( Employee, Employee[HireDate] <= CALCULATE ( MAX ( 'Date Dimension'[Date] ), FILTER( Employee, Employee[HireDate] >= MIN( 'Date Dimension'[Date])))))
Above formula returns the total of employee in the organization for the selected period and value is right.
Now, I tried to calculate the # of New Hires however I didn't able to figure it out to calculate only new employee joined for that particualr period.
Any help will be appricieted.
Solved! Go to Solution.
Another way to do this would be to create a New Table (click New Table on the Modeling tab)
I call this the @OwenAuger GENERATE method
Employee Hiring History = SUMMARIZE ( GENERATE ( EmployeesTable, CALCULATETABLE ( VALUES ( 'CalendarTable'[Date] ), DATESBETWEEN ( 'CalendarTable'[Date], EmployeesTable[Hire Date], EmployeesTable[Termination Date] ) ) ), EmployeesTable[Employee Name], 'CalendarTable'[Date] )
Here's the Measure for this table
Active Employees = CALCULATE ( DISTINCTCOUNT ( 'Employee Hiring History'[Employee Name] ), FILTER ( ALL ( 'Employee Hiring History'[Date] ), 'Employee Hiring History'[Date] <= MAX ( 'Employee Hiring History'[Date] ) ) )
This method also works and on the daily level both SUMMARIZE and USERELATIONSHIP produce the same results
However when you move up to the monthly level - people that have been terminated this month are still counted for the whole month with the SUMMARIZE method
Case in point Feb 2017 Emp 14 was terminated however will be included with the summarize method!
So which way you go will depend in your requirement...
Here's one way of doing this...
1) Create a CalendarTable
2) Relate Hire Date to CalendarTabele[Date] and then Termination Date to CalendarTable[Date] also
(second relationship will be inactive indcated by a dotted line)
3) create these 4 Measures
Hired = COUNTA ( EmployeesTable[Hire Date] ) Terminated = CALCULATE ( COUNTA ( EmployeesTable[Termination Date] ), USERELATIONSHIP ( CalendarTable[Date], EmployeesTable[Termination Date] ) ) Balance = [Hired] - [Terminated] Overall Current Employees = CALCULATE ( [Balance], FILTER ( ALL(CalendarTable[Date]), CalendarTable[Date] <= MAX ( CalendarTable[Date] ) ) )
Here's the result...
Hope this helps!
Another way to do this would be to create a New Table (click New Table on the Modeling tab)
I call this the @OwenAuger GENERATE method
Employee Hiring History = SUMMARIZE ( GENERATE ( EmployeesTable, CALCULATETABLE ( VALUES ( 'CalendarTable'[Date] ), DATESBETWEEN ( 'CalendarTable'[Date], EmployeesTable[Hire Date], EmployeesTable[Termination Date] ) ) ), EmployeesTable[Employee Name], 'CalendarTable'[Date] )
Here's the Measure for this table
Active Employees = CALCULATE ( DISTINCTCOUNT ( 'Employee Hiring History'[Employee Name] ), FILTER ( ALL ( 'Employee Hiring History'[Date] ), 'Employee Hiring History'[Date] <= MAX ( 'Employee Hiring History'[Date] ) ) )
This method also works and on the daily level both SUMMARIZE and USERELATIONSHIP produce the same results
However when you move up to the monthly level - people that have been terminated this month are still counted for the whole month with the SUMMARIZE method
Case in point Feb 2017 Emp 14 was terminated however will be included with the summarize method!
So which way you go will depend in your requirement...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |