Forum Discussion

Megha2498's avatar
Megha2498
Frequent Visitor
2 years ago

undefined

Hi All,   I need to create a power BI with attrition rate calculation that can drill down by Department, country and manager. I have two files, headcount and Termination.  Need help in DAX
Megha2498's avatar
Megha2498
Frequent Visitor
2 years ago

Here is the sample data:

Active employee table:

Reporting DateEmployee IDNameDepartmentCountry
12/31/2023Empy 1AFinanceIndia
12/31/2023Empy 2BHRFinland
12/31/2023Empy 3CSupplyUS
12/31/2023Empy 4DFinanceCanada
12/31/2023Empy 5EFinanceIndia
12/31/2023Empy 6FHRFinland
12/31/2023Empy 7GSupplyUS
12/31/2023Empy 8HITCanada
12/31/2023Empy 9IITIndia
12/31/2023Empy 10JITFinland
12/31/2023Empy 11KSupplyUS
12/31/2023Empy 12LHRCanada
12/31/2023Empy 13MFinanceCanada
1/31/2024Empy 1AFinanceIndia
1/31/2024Empy 2BHRFinland
1/31/2024Empy 3CSupplyUS
1/31/2024Empy 4DFinanceCanada
1/31/2024Empy 5EFinanceIndia
1/31/2024Empy 6FHRFinland
1/31/2024Empy 7GSupplyUS
1/31/2024Empy 8HITCanada
1/31/2024Empy 9IITIndia
1/31/2024Empy 11KSupplyUS
1/31/2024Empy 12LHRCanada
1/31/2024Empy 13MFinanceCanada
2/29/2024Empy 1AFinanceIndia
2/29/2024Empy 2BHRFinland
2/29/2024Empy 5EFinanceIndia
2/29/2024Empy 6FHRFinland
2/29/2024Empy 7GSupplyUS
2/29/2024Empy 8HITCanada
2/29/2024Empy 9IITIndia
2/29/2024Empy 11KSupplyUS
2/29/2024Empy 12LHRCanada
2/29/2024Empy 13MFinanceCanada
2/29/2024Empy 14NHRIndia

 

Terminated employees table:

Employee IDNameDepartmentCountryTermination date
Empy 3CSupplyUS2/2/2024
Empy 4DFinanceCanada2/15/2024
Empy 10JITFinland1/5/2024

 

Expected calculation:

Monthly Attrition Rate calculation (Dec - Jan)

12/31/2023Opening HC13
1/31/2024Closing HC12
Dec - JanTerminations1
  1/average (13,12)
 Attrition rate8.0%

 

YTD Attrition Rate calculation (Dec - Feb)

12/31/2023Opening HC13
2/29/2024Closing HC11
Dec - JanTerminations3
  3 /average (13,11)
 Attrition rate25.0%

 

I should be able to filter this calculation by country and department.  

talespin's avatar
talespin
Solution Sage
2 years ago

Incorrect.

  • Megha2498's avatar
    Megha2498
    Frequent Visitor
    2 years ago

    The data is coming two different tables, should I combine to use this solution?

    • talespin's avatar
      talespin
      Solution Sage
      2 years ago
      Incorrect
      • Megha2498's avatar
        Megha2498
        Frequent Visitor
        2 years ago

        YTD Calculation is not helping. 

        The data is in two tables. "Active employee list" and "termination List"