Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

track employees movement

Hi everyone, 

I have a table <employee> that lists the monthly status of each employee as the example below.
I have a date table to select analysis period 
Imagine, the selected period is 012023 & 022023, i have this formula to compute the mobility rate :
DistinctCount(employeee[id]) within the selected period = 122022 / DistinctCount(employeee[id]) at the previous month of the StartMonth of the selected period = 122022 *100 = 3/4*100

I need after that to evaluate that rate by gender, by department and naturally there are a lot of other demographic, geographic and organisationnal caracteristics that I ommitted here to make it simple

For example, when I filter by gender (Gender =M), I need to get 2/3*100

Here is an example of the employee table :
month,id,gender,current_department,mobility,previous_department
122022,A,F,D1,1,null
122022,B,M,D1,0,D1
122022,C,M,D2,0,D2
122022,D,M,D1,0,D1
012023,A,F,D1,0,D1
012023,B,M,D2,1,D1
012023,C,M,D2,0,D2
012023,D,M,D1,0,D1
012023,E,F,D1,1,null
022023,A,F,D1,0,D1
022023,B,M,D2,0,D2
022023,C,M,null,1,D2
022023,D,M,D1,0,D1
022023,E,F,D1,0,D1


Thanks a lot community !

Need Help
Tips and Tricks

3 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    2 years ago

    Anonymous 

    Define a Measure

    DistinctEmployeesSelectedPeriod = DISTINCTCOUNT('Employee'[id])

    Define a Measure to Calculate the Distinct Count of Employees in the Previous Period:

    DistinctEmployeesPreviousMonth = 
    VAR StartMonth = MIN('DateTable'[Month]) 
    VAR PreviousMonth = EOMONTH(STARTOFMONTH('DateTable'[Date]), -1)
    RETURN
    CALCULATE(
    DISTINCTCOUNT('Employee'[id]),
    'Employee'[month] = FORMAT(PreviousMonth, "MMyyyy")
    )

    Create a Mobility Rate Measure:

    MobilityRate = 
    DIVIDE(
    [DistinctEmployeesSelectedPeriod] - [DistinctEmployeesPreviousMonth],
    [DistinctEmployeesPreviousMonth],
    0
    ) * 100

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks a lot for your answer
      The real issue is when I filter the departments (current_department = D1), I will not capture the attrition mobility example 012023,B,M,D2,1,D1
      and I have a lot of other slicers 
      Thanks again for any idea 🙂

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    Hi Anonymous - you have to calculate few measure to acheive the same

     

    create a measure to calculate total employee Count in the Selected Period

    TotalEmployeeCount_SelectedPeriod =
    CALCULATE(
    DISTINCTCOUNT(employee[id]),
    employee[month] IN {"012023", "022023"}
    )

     

    another measure , to calculate Total Employee Count in the Previous Month

    TotalEmployeeCount_PreviousMonth =
    CALCULATE(
    DISTINCTCOUNT(employee[id]),
    employee[month] = "122022"
    )

     

    define a measure to calculate the mobility rate

    MobilityRate =
    DIVIDE(
    [TotalEmployeeCount_SelectedPeriod],
    [TotalEmployeeCount_PreviousMonth],
    1
    ) * 100

     

    you have the mobility rate measure set up, add it to your visuals. Then, apply slicers or filters for gender, department, and other attributes in Power BI,

     

    Hope this works as expected. please check.