undefined

[Megha2498]

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]

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]

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]

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]

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?

[talespin]

Incorrect.

[Megha2498]

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

[talespin]

Incorrect

[Megha2498]

YTD Calculation is not helping. 

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

[talespin]

hi @Megha2498 

Please ignore above measure, will repost.

[lbendlin]

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

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/1447...