Forum Discussion

mizio's avatar
mizio
Frequent Visitor
4 years ago

Calculate attrition per date

I have an employees table with start/end date and attrition date if they leave before terms.

In orde to create some visualization, i would need to know, for each calendar day, the n. of attritions, the number of active employees and the percentage.

I have my data like here:
Calendar

Calendar
Date
01-Jan-22
02-Jan-22
03-Jan-22
04-Jan-22
05-Jan-22
06-Jan-22
07-Jan-22
08-Jan-22
09-Jan-22
10-Jan-22

 

Attrition

Name	Attrition Date	Started	       Ended
ABC123		        31-Dec-21	01-Jun-22
DEF345	04-Jan-22	31-Dec-21	01-Jun-22
ABC124		        31-Dec-21	05-Jan-22
DEF346	07-Jan-22	02-Jan-22	31-Dec-22
ABC125		        02-Jan-22	08-Jan-22
DEF347		        02-Jan-22	31-Dec-22
ABC126	07-Jan-22	31-Dec-21	01-Jun-22
DEF348		        31-Dec-21	31-Dec-22
ABC127		        02-Jan-22	09-Jan-22
DEF349	09-Jan-22	31-Dec-21	31-Dec-22

 

And the resulting table should be

Date		AttritionCount		Active Resources	%Attrition
01-Jan-22	0				6	                  0%
02-Jan-22	0				10	                  0%
03-Jan-22	0				10	                  0%
04-Jan-22	1				10	                  9%
05-Jan-22	0				8	                  0%
06-Jan-22	0				8	                  0%
07-Jan-22	2				5	                 29%
08-Jan-22	0				4	                  0%
09-Jan-22	1				2	                 33%
10-Jan-22	0				2	                  0%

I am really stuck in trying to use summarize, rollup, addocolumns... any help would be appreciated.

 

thanks!

Need Help

2 Replies

  • Jeanxyz's avatar
    Jeanxyz
    Icon for Power Participant rankPower Participant
    4 years ago

    You don't need to calculate number of attritions each day. 

    Here is how I calculate 3-month attrition rate. Please upload a sample table if that doesn't work for you. 

     

    *HC represent headcount

    *employee_summarize table is the table with hiring and terminate date of each employee. 

     

    Rolling3M attrition% =
    var max_day=max(Dim_Date[Date])
    var min_day=date(year(EOMONTH(max_day,-2)),month(EOMONTH(max_day,-2)),1)

    var HC _start=calculate(distinctcount(employee_summarize[Employee Name]),employee_summarize[Hiring Date]<=min_day && employee_summarize[Termination Date]>=min_day)

    var HC_end=calculate(distinctcount(employee_summarize[Employee Name]),employee_summarize[Hiring Date]<=max_day && employee_summarize[Termination Date]>=max_day)
    Var HC=(HC_start + HC_end)/2
    Var leaver_=calculate(distinctcount(employee_summarize[Employee Name]),all(dim_date),employee_summarize[Date Key]>=min_day, employee_summarize[Date Key]<=max_day, employee_summarize[Leaver]=1)
    Return
    Divide(leaver_,HC)
  • mizio's avatar
    mizio
    Frequent Visitor
    4 years ago

    Thanks, I was really lookign a way to consolidate everything in a table, summarized. I tried using your apprpach above, but didn't work.