Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
jdevries192
Frequent Visitor

DAX: Calculate Hires and Terminations

Hi All,

 

Looking to calculate with DAX new hire retention in a given month based on an activity table. Issue is an employee may be hired and terminated many times (see employee 1 in example below). So I can't pivot the table to determine hire and termination dates in a given timeframe.

 

Calculation is (Ending Month's Continuous Headcount / Starting Month's Headcount)

 

Example Data below:

EmployeeActivityDate
1Hire1/1/2022
2Hire1/1/2022
1Termination1/5/2022
3Hire1/31/2022
4Hire1/31/2022
3Termination2/2/2022
1Hire2/5/2022
2Termination2/5/2022
1Termination2/6/2022
1Hire2/20/2022

 

For February, Employees 2, 3, and 4 were active at the start of the month. But only Employee 4 was active at the start and end of the month. So the calculation would be:

 

1/3 = 33%.

 

Any help would be appreciated.

 

 

 

2 REPLIES 2
amitchandak
Super User
Super User

@jdevries192 , With help from date table joined to your date of the table

 

Active Employees = calculate(distinctCOUNT(EmpData[ID]) , filter(EmpData, EmpData[Date] <= max(Date[Date]) && [Activity] = "Hire" ))
- calculate(distinctCOUNT(EmpData[ID]) , filter(EmpData, EmpData[Date] <= max(Date[Date]) && [Activity] = "Termination" ))

@amitchandak 

You are counting all hires and terminations.

 

I need those who are still active at the start of February (who have been hired but not terminated). And I need those who were active at the start of February and who are still active at the end of February.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors