Calculate HR Attrition Rate ( Without Termination Date)

[Raman3456]

Hello Everyone ,

Hope you are doing well. 

I have a data 3 columns (Employee ID, Name , Date) .. I need a DAX formula which is like 

 1) present in Last Month

, 2) Present in Current_Month

 3) Present in next month .

Please consider there is no termination date or other dates .

Regards,

Ramana PC

[Anonymous · Accepted solution]

Thanks for the replies from FreemanZ and Kedar_Pande.

Hi @Raman3456 ,

Based on your description I created simple data:

Then create measures:

present in Last Month = CALCULATE(DISTINCTCOUNT('Table'[Employee ID]),FILTER('Table','Table'[Date]>=EOMONTH(TODAY(),-2)+1&&'Table'[Date]<=EOMONTH(TODAY(),-1)))

Present in Current_Month = CALCULATE(DISTINCTCOUNT('Table'[Employee ID]),FILTER('Table','Table'[Date]>=EOMONTH(TODAY(),-1)+1&&'Table'[Date]<=EOMONTH(TODAY(),0)))

Present in next month = CALCULATE(DISTINCTCOUNT('Table'[Employee ID]),FILTER('Table','Table'[Date]>=EOMONTH(TODAY(),0)+1&&'Table'[Date]<=EOMONTH(TODAY(),1)))

HR Attrition Rate = DIVIDE([Present in Current_Month],[present in Last Month])

Forecast HR Attrition Rate = DIVIDE([Present in next month],[Present in Current_Month])

You can also use cards to display the names of absent employees, such as:

NextLostEmployee = 
VAR _CurrentEmployee=CALCULATETABLE(VALUES('Table'[Name]),FILTER('Table','Table'[Date]>=EOMONTH(TODAY(),-1)+1&&'Table'[Date]<=EOMONTH(TODAY(),0)))
VAR _NextEmployee=CALCULATETABLE(VALUES('Table'[Name]),FILTER('Table','Table'[Date]>=EOMONTH(TODAY(),0)+1&&'Table'[Date]<=EOMONTH(TODAY(),1)))
VAR _LostEmployee=EXCEPT(_CurrentEmployee,_NextEmployee)
RETURN CONCATENATEX(_LostEmployee,'Table'[Name],"/")

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[Raman3456]

Here my fields are 

Employee ID , Name , Date 

I need the solution like 

If any employee which will not be available in next month consider it as attrition .

So .... I need a proper dax (to find present, past and next ) based on employee ID .

[Kedar_Pande]

@Raman3456 

You can create measures for each condition based on the Date column

Present_LastMonth = 
VAR LastMonthStart = DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1)
VAR LastMonthEnd = EOMONTH(LastMonthStart, 0)
RETURN 
IF(
CALCULATE(COUNTROWS('Table'), 'Table'[Date] >= LastMonthStart, 'Table'[Date] <= LastMonthEnd) > 0,
1,
0
)

Present_CurrentMonth = 
VAR CurrentMonthStart = DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
VAR CurrentMonthEnd = EOMONTH(CurrentMonthStart, 0)
RETURN 
IF(
CALCULATE(COUNTROWS('Table'), 'Table'[Date] >= CurrentMonthStart, 'Table'[Date] <= CurrentMonthEnd) > 0,
1,
0
)

Present_NextMonth = 
VAR NextMonthStart = EOMONTH(TODAY(), 0) + 1
VAR NextMonthEnd = EOMONTH(TODAY(), 1)
RETURN 
IF(
CALCULATE(COUNTROWS('Table'), 'Table'[Date] >= NextMonthStart, 'Table'[Date] <= NextMonthEnd) > 0,
1,
0
)

You can add these measures to your report to evaluate the presence of employees for the specified time frames.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[FreemanZ]

Hi @Raman3456 ,

try like:

PresentThisMonth =

CALCULATE(

    COUNTROWS(data),

    FILTER(

        ALL(Data[date]),

        EOMONTH(Data[date], 0) = EOMONTH(TODAY(), 0)

    )

)

PresentLastMonth =

CALCULATE(

    COUNTROWS(data),

    FILTER(

        ALL(Data[date]),

        EOMONTH(Data[date], 0) = EOMONTH(TODAY(), -1)

    )

)

PresentNextMonth =

CALCULATE(

    COUNTROWS(data),

    FILTER(

        ALL(Data[date]),

        EOMONTH(Data[date], 0) = EOMONTH(TODAY(), 1)

    )

)