Forum Discussion

JonV's avatar
JonV
Icon for Helper II rankHelper II
5 years ago

Trying to Create Headcount Measure for Employees over Time

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 gra...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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