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 every month and at the end of every year i need distinct count rather then showing them as sum. I have calculated but my bottom is not calculating right answer

 

Regards,

Sid

 
 
Need Help
  • johnt75's avatar
    johnt75
    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!

6 Replies

  • mh2587's avatar
    mh2587
    Icon for Super User rankSuper User
    4 years ago

    No of Hires = Distinctcount(tablename(hiredate))

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

    mh2587  i need monthly new hires. The measure you shared is only counting dates as a unqiue value

     

    I am explain my problem below

     

    Year   Month   totalStaff    NewHires

    2020   Jan         100              3

    2020   Feb         110              7

    2020   Mar         150              40

     

    I dont want running totals of new hires in each month.

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      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!

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    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