Forum Discussion

omkar_nag123's avatar
omkar_nag123
Regular Visitor
4 years ago

Attrition Rate Formula

Hi, amitchandak 

I'm trying to create Attrition Rate Dax,

Attrition Rate Formula% = No of employee who joined past 12 month / No of employee who have join last 12 month * 100

 

I've created Attrition Rate Dax so I got an error in this formula.

 

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    omkar_nag123 , refer if these formulae can help

     

    Hired Employee = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[Start Date],'Table'[Date]) )

     

    Terminated Employees = CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[End Date],'Table'[Date]),not(ISBLANK(Employee[End Date])))

     

    Hired Employee rolling 12 = calculate([Hired Employee], DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

     

    Terminated Employees rolling 12 = calculate([Terminated Employees], DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

    • omkar_nag123's avatar
      omkar_nag123
      Regular Visitor
      4 years ago

      Hi,

      Sorry I wrote wrong formula,

      below given is the right formula.

       

      Attrition Rate Formula% = No of employee who joined past 12 month and who left the company / No of employee who have join last 12 month * 100

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        omkar_nag123 , Hired and left

         

        Hired Employee rolling 12 , who has left too = calculate(calculate([Hired Employee], filter(Employee,  not(ISBLANK(Employee[End Date])) ) ) , DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    HI omkar_nag123,

    The error message appears when you try to return the wrong result type in the formula. For the current expression, dateadd function will return a list of values that measure not supported.

    If you want to get the last year date based on the current date value, you can try to use the date function to do these calculations.

    measure =
VAR currDate =
    MAX ( employee[HireDate] )
RETURN
    DATE ( YEAR ( currDate ) - 1, MONTH ( currDate ), DAY ( currDate ) )

    Regards,

    Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    HI omkar_nag123,

    Any update here? Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

    If these also don't help, please share more detailed information to help us clarify your scenario to test.

    How to Get Your Question Answered Quickly 

    Regards,

    Xiaoxin Sheng