How to calculate total of respective Year or Month as selected in Filter

[jdoshi65]

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.

[Sean]

@jdoshi65

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!

[Sean · Accepted solution]

@jdoshi65

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...