Forum Discussion
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
10 Replies
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Here is the sample data:
Active employee table:
Reporting Date Employee ID Name Department Country 12/31/2023 Empy 1 A Finance India 12/31/2023 Empy 2 B HR Finland 12/31/2023 Empy 3 C Supply US 12/31/2023 Empy 4 D Finance Canada 12/31/2023 Empy 5 E Finance India 12/31/2023 Empy 6 F HR Finland 12/31/2023 Empy 7 G Supply US 12/31/2023 Empy 8 H IT Canada 12/31/2023 Empy 9 I IT India 12/31/2023 Empy 10 J IT Finland 12/31/2023 Empy 11 K Supply US 12/31/2023 Empy 12 L HR Canada 12/31/2023 Empy 13 M Finance Canada 1/31/2024 Empy 1 A Finance India 1/31/2024 Empy 2 B HR Finland 1/31/2024 Empy 3 C Supply US 1/31/2024 Empy 4 D Finance Canada 1/31/2024 Empy 5 E Finance India 1/31/2024 Empy 6 F HR Finland 1/31/2024 Empy 7 G Supply US 1/31/2024 Empy 8 H IT Canada 1/31/2024 Empy 9 I IT India 1/31/2024 Empy 11 K Supply US 1/31/2024 Empy 12 L HR Canada 1/31/2024 Empy 13 M Finance Canada 2/29/2024 Empy 1 A Finance India 2/29/2024 Empy 2 B HR Finland 2/29/2024 Empy 5 E Finance India 2/29/2024 Empy 6 F HR Finland 2/29/2024 Empy 7 G Supply US 2/29/2024 Empy 8 H IT Canada 2/29/2024 Empy 9 I IT India 2/29/2024 Empy 11 K Supply US 2/29/2024 Empy 12 L HR Canada 2/29/2024 Empy 13 M Finance Canada 2/29/2024 Empy 14 N HR India Terminated employees table:
Employee ID Name Department Country Termination date Empy 3 C Supply US 2/2/2024 Empy 4 D Finance Canada 2/15/2024 Empy 10 J IT Finland 1/5/2024 Expected calculation:
Monthly Attrition Rate calculation (Dec - Jan)
12/31/2023 Opening HC 13 1/31/2024 Closing HC 12 Dec - Jan Terminations 1 1/average (13,12) Attrition rate 8.0% YTD Attrition Rate calculation (Dec - Feb)
12/31/2023 Opening HC 13 2/29/2024 Closing HC 11 Dec - Jan Terminations 3 3 /average (13,11) Attrition rate 25.0% I should be able to filter this calculation by country and department.
Incorrect.
The data is coming two different tables, should I combine to use this solution?
hi Megha2498
Please try these. Ignore previous measures as I did not take terminated employee count from Terminated table. Apology for that.
Note that these measures are based on assumption that dates in Active employee table will always be monthend dates and reporting is based on these dates and only for terminated employee count we need to refer to Terminated employee table.
---------------------------------------------------------------------------------------------------
Measure =VAR _MinDate = CALCULATE(MIN('ActiveEmployee'[Reporting Date]), REMOVEFILTERS('ActiveEmployee'[Reporting Date]))VAR _SelectedReportDt = SELECTEDVALUE('ActiveEmployee'[Reporting Date])VAR _PreviousMth = EOMONTH(_SelectedReportDt,-1)VAR _CurrentMthCount = CALCULATE(COUNT('ActiveEmployee'[Employee ID]))VAR _PrevMthCount = CALCULATE(COUNT('ActiveEmployee'[Employee ID]), REMOVEFILTERS('ActiveEmployee'[Reporting Date]), 'ActiveEmployee'[Reporting Date] = _PreviousMth)VAR _Terminated = CALCULATE(COUNT(Termination[Employee]), REMOVEFILTERS(), Termination[Termination date] >= _PreviousMth && Termination[Termination date] <= _SelectedReportDt)RETURN IF(_SelectedReportDt = _MinDate || NOT(HASONEFILTER('ActiveEmployee'[Reporting Date])),BLANK(),DIVIDE((_Terminated), ((_PrevMthCount+_CurrentMthCount)/2)))---------------------------------------------------------------------------------------------------
Measure YTD =VAR _MinDate = CALCULATE(MIN('ActiveEmployee'[Reporting Date]), REMOVEFILTERS('ActiveEmployee'[Reporting Date]), YEAR('ActiveEmployee'[Reporting Date]) = YEAR(TODAY()) )VAR _MaxDateLastYear = EOMONTH(_MinDate, -1)VAR _MaxDateCurrYr = CALCULATE(MAX('ActiveEmployee'[Reporting Date]), REMOVEFILTERS('ActiveEmployee'[Reporting Date]))VAR _CurrentCount = CALCULATE(COUNT('ActiveEmployee'[Employee ID]), REMOVEFILTERS(), 'ActiveEmployee'[Reporting Date] = _MaxDateCurrYr)VAR _PrevCount = CALCULATE(COUNT('ActiveEmployee'[Employee ID]), REMOVEFILTERS('ActiveEmployee'[Reporting Date]), 'ActiveEmployee'[Reporting Date] = _MaxDateLastYear)VAR _Terminated = CALCULATE(COUNT(Termination[Employee]), REMOVEFILTERS(), Termination[Termination date] >= _MaxDateLastYear && Termination[Termination date] <= _MaxDateCurrYr)RETURN DIVIDE((_Terminated), ((_PrevCount+_CurrentCount)/2))-------------------------------------------------------------------------------------------------------------Thankyou for quick response.
Sorry for confusion, the DAX is not helping.
Below is the corrected dataset:
I need help in creating 2 DAX.
1. Monthly Attrition Rate -> Total termination in previous month/ Average of (Previous HC + Current HC)
2. YTD / Cumulative Attrition Rate -> Total terminations from April 01 / Average of (HC in April 01 + Current HC)
Example:
Monthly Attrition Rate for Jun -> Terminations in Jun / Average (HC in May + HC in Jun)
YTD / Cumulative Attrition Rate for Jun -> Terminations From April - jun / Average ( HC in April + HC in Jun)
YTD / Cumulative Attrition Rate for Jul = Terminations From April - jul / Average ( HC in April + HC in Jul)
Below is the sample data:
Reporting Date Employee ID Employee Name Department Country Status Terminated Date 4/30/2023 Employee 1 ABC Finance Finland Active 4/30/2023 Employee 2 ABC HR US Active 4/30/2023 Employee 3 ABC IT Canada Active 4/30/2023 Employee 4 ABC SCM India Active 4/30/2023 Employee 5 ABC Finance Finland Active 4/30/2023 Employee 6 ABC IT US Terminated 4/2/2023 4/30/2023 Employee 7 ABC HR India Terminated 4/10/2023 5/31/2023 Employee 1 ABC Finance Finland Active 5/31/2023 Employee 2 ABC HR US Active 5/31/2023 Employee 3 ABC IT Canada Terminated 5/15/2023 5/31/2023 Employee 4 ABC SCM India Active 5/31/2023 Employee 5 ABC Finance Finland Active 5/31/2023 Employee 8 ABC IT US Active 5/31/2023 Employee 9 ABC HR India Active 5/31/2023 Employee 10 ABC HR India Active 5/31/2023 Employee 11 ABC HR India Active 5/31/2023 Employee 12 ABC HR India Active 5/31/2023 Employee 13 ABC HR India Active 5/31/2023 Employee 14 ABC HR India Active 6/30/2023 Employee 1 ABC Finance Finland Active 6/30/2023 Employee 2 ABC HR US Active 6/30/2023 Employee 4 ABC SCM India Active 6/30/2023 Employee 5 ABC Finance Finland Active 6/30/2023 Employee 8 ABC IT US Active 6/30/2023 Employee 9 ABC HR India Active 6/30/2023 Employee 10 ABC HR India Active 6/30/2023 Employee 11 ABC HR India Terminated 6/27/2023 6/30/2023 Employee 12 ABC HR India Terminated 6/27/2023 6/30/2023 Employee 13 ABC HR India Active 6/30/2023 Employee 14 ABC HR India Active 6/30/2023 Employee 15 ABC HR India Active 6/30/2023 Employee 16 ABC HR India Active 6/30/2023 Employee 17 ABC HR India Active 6/30/2023 Employee 18 ABC HR India Active 6/30/2023 Employee 19 ABC HR India Active 6/30/2023 Employee 20 ABC HR India Active 6/30/2023 Employee 21 ABC HR India Active 7/31/2023 Employee 1 ABC Finance Finland Terminated 7/1/2023 7/31/2023 Employee 2 ABC HR US Active 7/31/2023 Employee 4 ABC SCM India Active 7/31/2023 Employee 5 ABC Finance Finland Active 7/31/2023 Employee 8 ABC IT US Active 7/31/2023 Employee 9 ABC HR India Active 7/31/2023 Employee 10 ABC HR India Active 7/31/2023 Employee 13 ABC HR India Active 7/31/2023 Employee 14 ABC HR India Active 7/31/2023 Employee 15 ABC HR India Active 7/31/2023 Employee 16 ABC HR India Active 7/31/2023 Employee 17 ABC HR India Active 7/31/2023 Employee 18 ABC HR India Active 7/31/2023 Employee 19 ABC HR India Active 7/31/2023 Employee 20 ABC HR India Active 7/31/2023 Employee 21 ABC HR India Active
