Forum Discussion

omkar_nag123's avatar
omkar_nag123
Regular Visitor
4 years ago

Attrition Rate

Hi, Community I have HR sample data, I'm trying to calculate Attrition Rate  Following are the images of my data, Can you suggest any Dax from the below data.       
Need Help
omkar_nag123's avatar
omkar_nag123
Regular Visitor
4 years ago

Hi amitchandak 

I've hire date column.

how to calculate Terminate and Current Employees

 

Ps: I'm new to power bi

amitchandak's avatar
amitchandak
Super User
4 years ago

omkar_nag123 , with help from a date table , you can do that. Refer steps in blog

 

Hired Employee = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[Start Date],'Date'[Date]) )

Terminated Employees = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[End Date],'Date'[Date]),not(ISBLANK(Employee[End Date])))

Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

 

Date table has active join with hire date and inactive join with end date

 

Please find the file attached after signature

 

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    omkar_nag123 ,

    refer if these formulae can help

     

    Hired Employee = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[Start Date],'Table'[Date]) )

     

    Terminated Employees = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[End Date],'Table'[Date]),not(ISBLANK(Employee[End Date])))

     

    Hired Employee rolling 12 = calculate([Hired Employee], DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

     

    Terminated Employees rolling 12 = calculate([Terminated Employees], DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))