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...
  • v-zhangti's avatar
    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.