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...

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 ()
        )
    )