Forum Discussion

KeyFMSPM's avatar
KeyFMSPM
New Member
3 years ago
Solved

Needing help with New measure using date range

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.

 

 

  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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.

4 Replies