Forum Discussion

Siddiq8686's avatar
Siddiq8686
Icon for Helper I rankHelper I
4 years ago
Solved

Employee New Hire

Hello tamerj1  and Others,   I need to calculate new employees hired for the each respective month by using their HireDate   For example I need to calculate how many new employees got hired in ev...
  • johnt75's avatar
    4 years ago

     

    New Hires =
    var minDate = MIN('Date'[Date])
    var maxDate = MAX('Date'[Date])
    return CALCULATE( COUNTROWS( 'Staff' ),
    REMOVEFILTERS( 'Date'[Date])
    'Staff'[HireDate] <= maxDate && 'Staff'[Hire Date] >= minDate
    )

    Should work at any level of the date hierarchy

  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Siddiq8686 ,
    First you need to build a new "inactive" relationship like this

    Your measures would be

    New Hires = 
    CALCULATE ( 
        COUNTROWS ( VALUES ( Payroll_Merge_File[Employee #] ) ),
        USERELATIONSHIP ( Calendar_Table[Date],Payroll_Merge_File[Hire Date] )
    )
    Total Staff = 
    VAR LastDateInFilter = 
        CALCULATE ( 
            MAX (Payroll_Merge_File[Hire Date] ),
            USERELATIONSHIP ( Calendar_Table[Date],Payroll_Merge_File[Hire Date] )
        )
    VAR Result =
        CALCULATE ( 
            COUNTROWS ( VALUES ( Payroll_Merge_File[Employee #] ) ),
            USERELATIONSHIP ( Calendar_Table[Date],Payroll_Merge_File[Hire Date] ),
            Calendar_Table[Date] <= LastDateInFilter
        )
    RETURN
        Result

    Your report shall look like this

    Please note that currently your Calendar Table starts from 2019 while hiring data starts from 1976. Therefore you need to update you Calendat Table to include all missing dates.
    Hope this answers your query. Have a great day!