Forum Discussion

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

Frequent Visitor

2 years ago

Here 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.

talespin

Solution Sage

2 years ago

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))

-------------------------------------------------------------------------------------------------------------

Megha2498

Frequent Visitor

2 years ago

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

talespin
Solution Sage
2 years ago
hi Megha2498

Please share 2024 data and end result you expect.

Also please comfirm if you are using single table or multiple tables like before?