topic Re: Calculate the Attrition on the Employee (No termination date and No status) in DAX Commands and Tips

Calculate the Attrition on the Employee (No termination date and No status)

Raman3456 — Fri, 29 Nov 2024 06:02:35 GMT

Hello Everyone ,

Hope you are doing well.

Created Date	Employee ID
10-01-2024	SD11
10-01-2024	SD12
10-02-2024	SD13
10-02-2024	SD14
10-02-2024	SD12
10-03-2024	SD11
10-03-2024	SD15
10-04-2024	SD12
10-04-2024	SD11
10-05-2024	SD15
10-05-2024	SD23
10-06-2024	SD11
10-06-2024	SD15
10-07-2024	SD15
10-07-2024	SD11
10-07-2024	SD14
10-08-2024	SD11
10-08-2024	SD14
10-09-2024	SD11
10-09-2024	SD13
10-10-2024	SD11
10-10-2024	SD11
10-11-2024	SD13
10-11-2024	SD11
10-11-2024	SD11
10-12-2024	SD13

I have a Employee table , Need a measure or column

If the employee January month and let the orgnization in febraury we can call it as Attition

Please help me with measures

present in last month

Present in Current Month

Present in Next month

Best Regards,

Ramana PC

Re: Calculate the Attrition on the Employee (No termination date and No status)

Sahir_Maharaj — Mon, 02 Dec 2024 22:52:22 GMT

Hello Raman3456,

Can you please try the following:

1. First, create a calculated column

MonthYear = FORMAT('Employee'[Created Date], "YYYY-MM")

2. Then, calculate the presence in the Current Month

Present in Current Month = CALCULATE( DISTINCTCOUNT('Employee'[Employee ID]), FILTER( 'Employee', FORMAT('Employee'[Created Date], "YYYY-MM") = FORMAT(MAX('Employee'[Created Date]), "YYYY-MM") ) )

3. Presence in the Previous Month

Present in Previous Month = CALCULATE( DISTINCTCOUNT('Employee'[Employee ID]), FILTER( 'Employee', FORMAT('Employee'[Created Date], "YYYY-MM") = FORMAT(DATEADD(MAX('Employee'[Created Date]), -1, MONTH), "YYYY-MM") ) )

4. Presence in the Next Month

Present in Next Month = CALCULATE( DISTINCTCOUNT('Employee'[Employee ID]), FILTER( 'Employee', FORMAT('Employee'[Created Date], "YYYY-MM") = FORMAT(DATEADD(MAX('Employee'[Created Date]), 1, MONTH), "YYYY-MM") ) )

5. Finally, calculate Attrition

Attrition = VAR PreviousMonthEmployees = CALCULATETABLE( DISTINCT('Employee'[Employee ID]), FILTER( 'Employee', FORMAT('Employee'[Created Date], "YYYY-MM") = FORMAT(DATEADD(MAX('Employee'[Created Date]), -1, MONTH), "YYYY-MM") ) ) VAR CurrentMonthEmployees = CALCULATETABLE( DISTINCT('Employee'[Employee ID]), FILTER( 'Employee', FORMAT('Employee'[Created Date], "YYYY-MM") = FORMAT(MAX('Employee'[Created Date]), "YYYY-MM") ) ) RETURN COUNTROWS( EXCEPT(PreviousMonthEmployees, CurrentMonthEmployees) )

Hope this helps.

Re: Calculate the Attrition on the Employee (No termination date and No status)

Raman3456 — Tue, 03 Dec 2024 05:09:36 GMT

Hi Thank you so much for your help ,

I have been following the list of measures but i am getting the below error .

Please let me know how to resolve it ?

Regards,

Ramana PC

Re: Calculate the Attrition on the Employee (No termination date and No status)

Irwan — Tue, 03 Dec 2024 05:20:18 GMT

looks like you "miss-wrote". see the yellow highlight.

seems there is no single tick at start and end of table name so it might be the cause of error.

Thank you.

Re: Calculate the Attrition on the Employee (No termination date and No status)

Raman3456 — Tue, 03 Dec 2024 05:51:03 GMT

Hi Imran,

Thank you for your suggestion , However it is giving same error .

Regards,

Ramana PC

Re: Calculate the Attrition on the Employee (No termination date and No status)

Poojara_D12 — Tue, 03 Dec 2024 05:52:29 GMT

Hi Raman3456

To calculate Attrition and employee presence across months in Power BI, follow these steps:

1. Create a Calendar Table
Ensure you have a Calendar table with continuous dates and a relationship with the Created Date column.

2. Measures
Present in Last Month

PresentLastMonth = CALCULATE( DISTINCTCOUNT(Employee[Employee ID]), PREVIOUSMONTH(Calendar[Date]) )

Present in Current Month

PresentCurrentMonth = CALCULATE( DISTINCTCOUNT(Employee[Employee ID]), MONTH(Calendar[Date]) = MONTH(TODAY()) )

Present in Next Month

PresentNextMonth = CALCULATE( DISTINCTCOUNT(Employee[Employee ID]), NEXTMONTH(Calendar[Date]) )

3. Attrition Measure

Attrition = IF( ISBLANK([PresentNextMonth]) && [PresentLastMonth] > 0, "Yes", "No" )

This should give you a clear view of attrition and employee presence across months.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS