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

[Raman3456]

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

[Poojara_D12]

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 

[Sahir_Maharaj]

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.

[Raman3456]

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

[Irwan]

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.

[Raman3456]

Hi Imran,

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

Regards,

Ramana PC