Trying to Create Headcount Measure for Employees over Time

[JonV]

Greetings,

I'm working on trying to write a measure that can do a distinct count of Employee IDs that works over time. In other words, I want to have the Year/Month on my axis, and see a line graph of the number of employees month/by month. Or drop it into another function seleting for the current date and getting a current headcount. Basic headcount stuff.

A few notes about the data:

1. Employee information always has a Hire date and has a Termination date when appropriate
2. If an employee changes departments, such as from Sales to Finance, their previous line is given an expiration date, a new line will be created with a creation date, but does not get new Hire/Termination dates.
3. Our company also had contingent workers that are treated separately. If an employee goes from being contingent to a full employee, their contigent line is given a termination date, and their new employee line gets a new hire date.

The model has a Date table. This is joined to a table with some of the employee information in it including the dates.

So I feel like my measure should be something along the lines of:

CALCULATE(
 DISTINCTCOUNT(Fact[EmployeeID]),
 Date[Date] >= Fact[HireDate] && Fact[EffectiveDate],
 Date[Date] <= Fact[TerminationDate] && Fact[ExpirationDate])

There an active join between the Date table and the Hire Date, and inactive joins to the other dates.

My trouble is that I can't seem to get Power BI to let me do the comparison between the dates. It recognizes the Date[Date], but then claims it can't find the others. If I flip it so that it's Fact[Hire Date] <= Date[Date], it finds the Fact[Hire Date] but not the other.

I saw this thread wherein someone suggested using a MAX/MIN function, but then I get the error where it's telling me MAX/MIN is being used in a True/False expression which isn't allowed. What can I do to make this measure work?

[v-eqin-msft]

Hi @JonV ,

Sorry for my late reply...

Please take a look at this blog for reference about the DAX error.

Since there is no enough data sample , I customized some simple data like this:

Then I merged these two tables based on Index column in order to facilitate filtering conditions:

Measure =
CALCULATE (
    DISTINCTCOUNT ( MergeTable[Fact.EmployeeID] ),
    FILTER (
        'MergeTable',
        'MergeTable'[Date] >= 'MergeTable'[Fact.HireDate]
            && 'MergeTable'[Date] >= 'MergeTable'[Fact.EffectiveDate]
            && 'MergeTable'[Date] <= 'MergeTable'[Fact.TerminationDate]
            && 'MergeTable'[Date] <= 'MergeTable'[Fact.ExpirationDate]
    )
)

My final result looks like this:

Here is my pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[MFelix]

Hi @JonV ,

The question in your measure is about the filter part on your calculation.

When  you are refering to:

 Date[Date] >= Fact[HireDate] && Fact[EffectiveDate]

OR

 Date[Date] <= Fact[TerminationDate] && Fact[ExpirationDate]

This is not returning any value since you are searching for Hire data greate than the selected date (this is correct) but then you add AND (&&) Effective date it't not picking up what you need because of the way the relationships are setup.

Without any data is difficult to give you the correct answer, but if you look at the post you refer the MAX and MIN are used within a filter expression that is what I think is missing here.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.