Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

Reply
Abhi1306
Regular Visitor

HR Dashboard Attrition Analysis

‎03-04-2025 05:16 AM

Hi, Community,

Kindly help with HR Attrition Calculation monthwise for the data below. Attrition Table required in the below format.

 

MonthClosingRegrettedEmployment Details Separation DateMonthCumulative Months & DaysAverage HCOverall Annualised Attrition%OverAll Regretted Annualised Regret%
Opening HC795       
 Closing of Active Head CountMonthly Regretted CountMonthly Terminated CountMonth CountCumulative Months & DaysAverage Active HC(Opening Count is fixed and adding monthwise Active HC)(Total Terminated Count/Avg Count)/Cumulative of Month*365(Total Regretted Count/Avg Count)/Cumulative of Month*365

Data

EMP IDEmployee NameEmployee StatusStatus RemarksDate of Joining Current CompanyGenderDiversityFunction NameEmployee LevelEmployee Salary GradeTypeEmployment Details Separation DateHi-Per/Hi-PotRetention Status
1526Emp1526ActiveActive01-Apr-24MaleMaleOperationsLevel 1Senior Executive    
1527Emp1527ActiveActive01-Apr-24MaleMaleMarketing  & SalesLevel 3Senior General Manager    
1528Emp1528ActiveActive01-Apr-24MaleMaleOperationsLevel 1Senior Executive    
1529Emp1529ActiveActive01-Apr-24MaleMaleMarketing  & SalesLevel 1Deputy Manager    
1530Emp1530TerminatedTerminated01-Apr-24MaleMaleLegal & SecretarialLevel 3General ManagerNon-Regretted24-01-2025Non - Hiper/HipotNot Retained
1531Emp1531TerminatedTerminated01-Apr-24MaleMaleOperationsLevel 1Senior ExecutiveRegretted14-07-2024Non - Hiper/HipotNot Retained
1532Emp1532ActiveActive03-Apr-24FemaleFemaleMarketing  & SalesLevel 1Senior Executive    
1533Emp1533ActiveActive08-Apr-24FemaleFemaleMarketing  & SalesLevel 1Senior Executive    
1534Emp1534ActiveActive08-Apr-24MalePWDOperationsLevel 1Deputy ManagerRegretted Non - Hiper/HipotRetained
1535Emp1535ActiveActive10-Apr-24MaleMaleFinance & AccountsLevel 1Executive    
1536Emp1536ActiveActive10-Apr-24MaleMaleOperationsLevel 2Senior Manager    
1537Emp1537ActiveActive15-Apr-24MaleMaleMarketing  & SalesLevel 1Executive    
1538Emp1538ActiveActive15-Apr-24FemaleFemaleCustomer CentricityLevel 1Executive    
1539Emp1539ActiveActive22-Apr-24FemaleFemaleOperationsLevel 1Assistant Manager    
1540Emp1540ActiveActive22-Apr-24MaleMaleOperationsLevel 1Senior Executive    
1541Emp1541ActiveActive24-Apr-24MaleMaleOperationsLevel 1Deputy Manager    
1542Emp1542ActiveActive29-Apr-24FemaleFemaleMarketing  & SalesLevel 1Senior Executive    
1543Emp1543ActiveActive02-May-24MaleMaleMarketing  & SalesLevel 1Senior Executive    
1544Emp1544ActiveActive06-May-24FemaleFemaleOperationsLevel 1Senior Executive    
1545Emp1545ActiveActive06-May-24MaleMaleOperationsLevel 1Assistant Manager    
1546Emp1546ActiveActive06-May-24FemaleFemaleOperationsLevel 1Assistant Manager    
1547Emp1547TerminatedTerminated08-May-24FemaleFemaleCustomer CentricityLevel 1Senior ExecutiveNon-Regretted12-05-2024Non - Hiper/HipotNot Retained
1548Emp1548ActiveNotice Period13-May-24FemaleFemaleMarketing  & SalesLevel 1Senior ExecutiveRegretted15-03-2025Non - Hiper/HipotRetained
1549Emp1549ActiveActive13-May-24MaleMaleOperationsLevel 1Senior Executive    
1550Emp1550ActiveActive20-May-24FemaleFemaleCustomer CentricityLevel 1Executive    
1551Emp1551ActiveActive20-May-24MaleMaleOperationsLevel 1Senior Executive    
1552Emp1552ActiveActive20-May-24MaleMaleMarketing  & SalesLevel 1Assistant Manager    
1553Emp1553ActiveActive22-May-24MaleMaleOperationsLevel 2Senior Manager    
1554Emp1554ActiveActive27-May-24FemaleFemaleFinance & AccountsLevel 1Assistant Manager    
1555Emp1555ActiveActive29-May-24FemaleFemaleCustomer CentricityLevel 1Executive    
1556Emp1556ActiveActive03-Jun-24MaleLGBTQCustomer CentricityLevel 1Senior Executive    
1557Emp1557ActiveActive03-Jun-24FemaleFemaleGallopersLevel 2Manager    
1558Emp1558TerminatedTerminated05-Jun-24FemaleFemaleCustomer CentricityLevel 2Senior ManagerRegretted28-10-2024Non - Hiper/HipotNot Retained
1559Emp1559ActiveActive05-Jun-24FemaleFemaleCustomer CentricityLevel 1Senior Executive    
1560Emp1560ActiveActive05-Jun-24FemaleFemaleCustomer CentricityLevel 1Senior Executive    
Labels:
Message 1 of 11
352 Views
10 REPLIES 10
Abhi130681
Frequent Visitor

‎04-22-2025 02:56 AM

Hi @v-hashadapu I want the above table, with given data

Message 10 of 11
100 Views
0
v-hashadapu
Community Support
Community Support
In response to Abhi130681

‎04-25-2025 01:16 AM

Hi @Abhi1306 , I went through the thread and it would be better if you could attach the .pbix file.

I know you have asked us to do this with given data. But why you are not getting desired result can be due to data modelling issue or other issue which can only be checked if you share the file.

Message 11 of 11
73 Views
v-hashadapu
Community Support
Community Support

‎04-22-2025 12:02 AM

Hi @Abhi1306 , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

Message 9 of 11
115 Views
0
v-hashadapu
Community Support
Community Support

‎04-09-2025 04:38 AM

Hi @Abhi1306 , Thank you for reaching out to the Microsoft Community Forum.

Please let us know if your issue is solved. If it is, consider marking the answers that helped 'Accept as Solution', so others with similar queries can find them easily. If not, please share the details.
Thank you.

Message 6 of 11
160 Views
0
Abhi130681
Frequent Visitor
In response to v-hashadapu

‎04-09-2025 04:59 AM

Hey,

 

Thank you for response, 

With reference to below, I have not recevied the result as required, 

Message 7 of 11
152 Views
0
v-hashadapu
Community Support
Community Support
In response to Abhi130681

‎04-09-2025 05:06 AM

Can you please confirm if you @Abhi1306  is the same as @Abhi130681 . Also you can add .pbix files by clicking on the 'browse' button. Refer below screensho.

vhashadapu_0-1744200400691.png

 

Message 8 of 11
148 Views
0
Abhi130681
Frequent Visitor

‎03-11-2025 03:01 AM

Screenshot 2025-03-11 153553.jpg

 

 

 

I am not able to get desired result, I want monthwise Attrition Precentage, and below is calculation formate : 

 

MonthClosingRegrettedEmployment Details Separation DateMonthCumulative Months & DaysAverage HCOverall Annualised Attrition%OverAll Regretted Annualised Regret%
Opening HC795       
 Closing of Active Head CountMonthly Regretted CountMonthly Terminated CountMonth CountCumulative Months & DaysAverage Active HC(Opening Count is fixed and adding monthwise Active HC)(Total Terminated Count/Avg Count)/Cumulative of Month*365(Total Regretted Count/Avg Count)/Cumulative of Month*365
Message 4 of 11
239 Views
Abhi130681
Frequent Visitor
In response to Abhi130681

‎03-11-2025 03:08 AM

how do I attached .pbix file 

Message 5 of 11
232 Views
DataNinja777
Super User
Super User

‎03-04-2025 07:14 AM

Hi @Abhi1306 ,

 

The HR attrition calculation is now fully dynamic, relying entirely on the EmployeeData table without any hardcoded values. The Opening HC is determined for each month by counting employees who joined on or before the start of the month and who have not yet left. The Closing HC is calculated in a similar manner but considers the end of the month instead. Monthly terminations are determined by counting employees whose Separation Date falls within the given month. Additionally, regretted terminations are filtered by checking the Regretted flag. The cumulative months are tracked dynamically based on the earliest recorded month in the dataset, ensuring an accurate timeline for attrition calculations. The average headcount is computed as the midpoint between the opening and closing counts. Finally, the overall annualized attrition rate and regretted attrition rate are derived using the total terminations and average headcount, scaled to a yearly basis.

Opening HC = 
VAR FirstMonth = SELECTEDVALUE('Calendar'[Date])
RETURN 
    CALCULATE(
        COUNT('EmployeeData'[Employee ID]),
        'EmployeeData'[Date of Joining Current Company] <= FirstMonth &&
        (ISBLANK('EmployeeData'[Separation Date]) || 'EmployeeData'[Separation Date] > FirstMonth)
    )

Closing HC = 
VAR SelectedMonth = SELECTEDVALUE('Calendar'[Date])
RETURN 
    CALCULATE(
        COUNT('EmployeeData'[Employee ID]),
        'EmployeeData'[Date of Joining Current Company] <= EOMONTH(SelectedMonth, 0) &&
        (ISBLANK('EmployeeData'[Separation Date]) || 'EmployeeData'[Separation Date] > EOMONTH(SelectedMonth, 0))
    )

Monthly Terminations = 
CALCULATE(
    COUNT('EmployeeData'[Employee ID]),
    NOT(ISBLANK('EmployeeData'[Separation Date])) &&
    'EmployeeData'[Separation Date] >= STARTOFMONTH('Calendar'[Date]) &&
    'EmployeeData'[Separation Date] <= ENDOFMONTH('Calendar'[Date])
)

Monthly Regretted Terminations = 
CALCULATE(
    COUNT('EmployeeData'[Employee ID]),
    NOT(ISBLANK('EmployeeData'[Separation Date])) &&
    'EmployeeData'[Separation Date] >= STARTOFMONTH('Calendar'[Date]) &&
    'EmployeeData'[Separation Date] <= ENDOFMONTH('Calendar'[Date]) &&
    'EmployeeData'[Regretted] = TRUE()
)

Cumulative Months = 
VAR CurrentMonth = SELECTEDVALUE('Calendar'[Date])
RETURN 
    DATEDIFF(MIN('Calendar'[Date]), CurrentMonth, MONTH) + 1

Average HC = 
VAR OpenHC = [Opening HC]
VAR CloseHC = [Closing HC]
RETURN 
    (OpenHC + CloseHC) / 2

Overall Annualised Attrition% = 
VAR TotalTerminations = CALCULATE(SUMX(VALUES('Calendar'[Date]), [Monthly Terminations]))
VAR AvgHC = [Average HC]
VAR CumulativeMonths = [Cumulative Months]
RETURN 
    IF(CumulativeMonths = 0, BLANK(), (TotalTerminations / AvgHC) / CumulativeMonths * 365)

Overall Regretted Annualised Attrition% = 
VAR TotalRegretted = CALCULATE(SUMX(VALUES('Calendar'[Date]), [Monthly Regretted Terminations]))
VAR AvgHC = [Average HC]
VAR CumulativeMonths = [Cumulative Months]
RETURN 
    IF(CumulativeMonths = 0, BLANK(), (TotalRegretted / AvgHC) / CumulativeMonths * 365)

With this approach, the calculations dynamically adjust based on employee data, making them scalable and adaptable to any changes in headcount. The Opening HC and Closing HC update automatically each month based on hires and separations, ensuring that attrition is measured accurately. Using && ensures that multiple conditions are applied correctly, filtering employees based on both their Joining Date and Separation Date where applicable. This setup provides HR with a clear, data-driven view of workforce changes over time. Let me know if you need any further refinements.

 

Best regards,

 

Message 3 of 11
308 Views
bhanu_gautam
Super User
Super User

‎03-04-2025 07:07 AM

@Abhi1306 

Data Preparation: Ensure your data is clean and properly formatted. You may need to create additional columns for month and year based on the Employment Details Separation Date.

Create Calculated Columns:

Month: Extract the month from the Employment Details Separation Date.
Year: Extract the year from the Employment Details Separation Date.

 

And create measures as 

DAX
OpeningHC = 795

 

ClosingHC =
CALCULATE(
COUNTROWS('Table'),
'Table'[Employee Status] = "Active"
)

 

MonthlyRegrettedCount =
CALCULATE(
COUNTROWS('Table'),
'Table'[Status Remarks] = "Regretted",
'Table'[Employee Status] = "Terminated"
)

MonthlyTerminatedCount =
CALCULATE(
COUNTROWS('Table'),
'Table'[Employee Status] = "Terminated"
)

 

AverageActiveHC =
AVERAGEX(
VALUES('Table'[Month]),
CALCULATE(COUNTROWS('Table'), 'Table'[Employee Status] = "Active")
)

 

OverallAnnualisedAttrition =
DIVIDE(
[MonthlyTerminatedCount],
[AverageActiveHC]
) * 365 / [CumulativeMonths]

 

OverallRegrettedAnnualisedRegret =
DIVIDE(
[MonthlyRegrettedCount],
[AverageActiveHC]
) * 365 / [CumulativeMonths]




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!


LinkedIn






Message 2 of 11
309 Views

Helpful resources

Announcements
May PBI 25 Carousel

Power BI Monthly Update - May 2025

Check out the May 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.

View All