Needing help with New measure using date range

[KeyFMSPM]

I struggle with DAX big time and I've been struggling with trying to create a new measure that establishes the number of active users based on a "Hire date" and "Termination date".  What DAX function can I use to find active employee count  if I only have hire and term dates?   I am assuming that once I have this measure, I can use a slicer, for a specific period in time, and have an accurate headcount on said date.  Any help is appreciated.

[KeyFMSPM]

This did the trick!  Thank you.

[v-zhangti · Accepted solution]

Hi, @KeyFMSPM 

You can try the following methods.
Sample data:

Create a new calendar table.

Date = CALENDAR(MIN('Table'[Hire date]),TODAY())

Measure:

Measure = 
CALCULATE (
    COUNT ( 'Table'[Employee] ),
    FILTER ( ALL ( 'Table' ), [Termination date] = BLANK () )
)
    + CALCULATE (
        COUNT ( 'Table'[Employee] ),
        FILTER (
            ALL ( 'Table' ),
            [Hire date] >= MIN ( 'Date'[Date] )
                && [Termination date] <= MAX ( 'Date'[Date] )
                && [Termination date] <> BLANK ()
        )
    )

Please refer to the attachment.

Best Regards,

Community Support Team _Charlotte

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

[daXtreme]

Hi @KeyFMSPM 

There are myriads of questions on this forum related to your problem or even straight answers to it. There's a Search box in the upper right corner of the page. Please make use of it. On top of that, I'd strongly suggest that you read this first: How to Get Your Question Answered Quickly - Microsoft Power BI Community

It will seriously help you obtain good and quick solutions.

[amitchandak]

@KeyFMSPM , refer to my blog or video. Check the attached files

Power BI: HR Analytics - Employees as on Date: https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...