Formula Help- Distinct count related to multiple criteria

[ehmacc]

Hi everyone- 

I am trying to solve for an issue and have worked at it for way too long, need to finally ask for help. I am working on turnover metrics which requires Average Active employees for a denomenator, and number of terminations as a numerator. Terminations is not a problem. Equation works when I include ALL employees, but if I want to show turnover by department, my denomenator does not update and so my % is way off. 

I am dealing with two tables. Table 1 is a DimDate table joined, 1 to Many, to Table 2, a demographic table with dates related to employees. 

Goal: Show an average count of active employees in a timeframe, and have this number react to filters for department.

Current state: I can show the average count of active employees in a timeframe, but the number does not react to the filters on the page/on the visual for department. I have built my formula as a column in my Date Term table and then use a Measure for my visual which is impacted by filters for date ranges.

Current formula "count unique IDs as active if their hire date is before this date and they are either still active or their term date was after this day":

Active Employees =

CALCULATE (DISTINCTCOUNT ( Employment[EmployeeNumber] ),

FILTER (Employment, Employment[LastHire] <= 'Date Term'[Date]

&& OR (Employment[TerminationDate] > 'Date Term'[Date],ISBLANK ( Employment[TerminationDate] ) = TRUE ())))

Measure- Average Active Employees = CALCULATE(average('Date Term'[Active Employees]))

Ex Table 1: 'Date Term'

Date	FY	FY-Quarter	FY-Period	Active Employees (calc above)
5/31/2019	2019	2019-4	2019-12	1100
6/1/2019	2020	2020-1	2020-1	1101

Ex Table 2: 'Employment'

EmloyeeNumber	LastHire	TerminationDate	Department
111	1/1/2017		Finance
222	2/1/2005	8/1/2019	Legal

[dax]

Hi ehmacc,

If possible, could you please inform me more detailed information(such as your sample data and your expected output)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ehmacc]

@dax 

Links below. Easiest example would be using even just the emplyee count today, or the average this period. All company should be about 1025. But if I filtered it to Finance, I would expect 82, or 15 for HR. 

As mentioned before- The Measure for Terminations as part of my Turnover calcultaion works and interacts with filters for Department and the Period/Quarter just fine; 

Terms = CALCULATE(DISTINCTCOUNT(Employment[EmployeeNumber]), ISBLANK(Employment[TerminationDate]) = FALSE())

Employment- https://drive.google.com/file/d/1ehwNoLXvNRpUu2DJuaKxp1q_sX-0g6Mt/view?usp=sharing

DimDate- https://drive.google.com/file/d/16mYPQ8nbK7JLlp3cpPqwgmpqdIPsPVA5/view?usp=sharing

Apologies for using Google Drive. My organization has our One Drive as internally shareable only. I also had to format a column on my DimDate query once I loaded to make the join work for both dates to have the same fomat- 

JoinEmployment = FORMAT('Date Term'[Date], "MM-DD-YYYY")

[dax]

Hi ehmacc,

Did you mean you want to get interactive based on date range slicer? If so, you could try below measure

Measure =
VAR maxd =
    CALCULATE ( MAX ( 'Term Date'[Date] ), ALLSELECTED ( 'Term Date'[Date] ) )
VAR mind =
    CALCULATE ( MIN ( 'Term Date'[Date] ), ALLSELECTED ( 'Term Date'[Date] ) )
RETURN
    CALCULATE (
        COUNT ( Employment[Department] ),
        FILTER (
            Employment,
            Employment[Termination Date] < maxd
                && Employment[Termination Date] > mind
                || Employment[Termination Date] = BLANK ()
        )
    )

 If this is not what you want , please correct me and inform em your expected output.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ehmacc]

@dax

That equation only returns the count of employees with termination dates in that range, it isn't picking up employees without a termination date, which I need included.

I also had to change the filter to include Hire date as the first piece- it should filter for employees who were Hired before the last day of the date range who either had a term date greater than the first day of the date range or no term date. But this has not fixed the count problem- it is still only counting if they have a termination date. 

Measure =
VAR maxd =
    CALCULATE ( MAX ( 'Term Date'[Date] ), ALLSELECTED ( 'Term Date'[Date] ) )
VAR mind =
    CALCULATE ( MIN ( 'Term Date'[Date] ), ALLSELECTED ( 'Term Date'[Date] ) )
RETURN
    CALCULATE (
        COUNT ( Employment[Department] ),
        FILTER (
            Employment,
            Employment[Hire Date] < maxd
                && Employment[Termination Date] > mind
                || Employment[Termination Date] = BLANK ()
        )
    )

[amitchandak]

Can you calculate Avg Employee like

Total employee/Total Month.

That my works better