在PowerBI中进行员工司龄分析-优快云博客

员工司龄分析是企业人力资源管理中的重要环节，它不仅能帮助企业了解员工队伍的稳定性，还能为人才保留、薪酬福利设计等决策提供数据支持。本篇文章将介绍如何在PowerBI中实现员工司龄分析。

问题描述

为更好了解企业人员流动情况，现在需要进行员工司龄分析，请自行建模计算各年底时对应的司龄人数。

统计范围：年底时仍在职的员工，以及在本年离职的员工。

注意事项：

1、离职年份在以前的不用统计。

2、本年离职的员工中，统计时使用离职日期代替年底日期。

3、要到次年同一天才算满一年，比如2016/3/14入职，那么在2017/3/14时，司龄才为1。

具体问题如下图所示：

本案例的初始数据如下：

人员表：

ID	姓名	入职部门	入职日期	离职日期
1003	姓名3	部门3	2015年2月10日	2018年3月1日
1004	姓名4	部门4	2015年12月11日	2018年12月10日
1005	姓名5	部门1	2016年10月12日
1006	姓名6	部门2	2016年12月13日
1007	姓名7	部门3	2016年1月1日
1008	姓名8	部门4	2016年2月15日	2017年1月18日
1009	姓名9	部门1	2017年3月16日	2018年9月18日
1010	姓名10	部门2	2017年5月31日	2018年10月1日
1011	姓名11	部门3	2017年7月18日
1012	姓名12	部门4	2017年8月19日	2017年9月18日
1013	姓名13	部门1	2018年2月20日
1014	姓名14	部门2	2018年3月21日

司龄划分表：

序号	司龄	起始	结束
1	1年以下	0	0.99999999
2	1年 ~ 3年	1	2.99999999
3	3年以上	3	999999999

若需其它辅助表等，可自行创建并建模。

解题要点

1、员工的统计范围的查找逻辑为：对于当前统计的年份而言，其入职日期应在年底或之前，且离职日期为空或离职日期大于等于年初。

2、在统计各司龄区间人数前，需要计算出统计范围内的员工的司龄，而司龄的常见计算方法有以下三种：

数值格式日期的数学相减取整运算法。将起始与结束日期都转换成样式为yyyymmdd的数值，然后进行相减运算，最后除以10000并取整。
司龄关键日期节点列表法。先获取司龄为整数时的关键日期节点列表，然后从中找出最接近的一个关键日期所代表的司龄。
常规判断法。先将结束日期的年份减去起始日期的年份，得到粗略的司龄，然后如果结束月份小于起始月份，或者结束月份等于起始月份但是结束日期小于起始日期，则粗略的司龄需减一。

解决方案

首先创建一个日期表，并与人员表创建一对多关系，数据模型如下图所示：

然后下面是几种不同的实现方法，他们之间的差异主要是司龄的计算方法不同，具体的度量值表达式如下：

1、数值格式日期的数学相减取整运算法：

司龄划分-1 = 
SUMX(
    '司龄划分表',
    VAR Start_ = '司龄划分表'[起始]
    VAR End_ = '司龄划分表'[结束]
    VAR Employee_ = 
        CALCULATETABLE(
            '人员表',
            '日期表'[Date]<=MAX('日期表'[Date]),
            '人员表'[离职日期]=BLANK() || '人员表'[离职日期] >= MIN('日期表'[Date])
        )
    VAR AddCompanyYear = 
        ADDCOLUMNS(
            Employee_,
            "司龄",
                VAR OnCompany_Start = FORMAT('人员表'[入职日期],"yyyymmdd")*1
                VAR OnCompany_End = 
                    FORMAT(
                        MIN(
                            COALESCE('人员表'[离职日期],MAX('日期表'[Date])),
                            MAX('日期表'[Date])
                        ),
                        "yyyymmdd"
                    )*1
                RETURN
                QUOTIENT(OnCompany_End-OnCompany_Start,10000)
        )
    RETURN
    COUNTROWS(FILTER(AddCompanyYear,Start_<=[司龄] && [司龄]<=End_))
)

2、司龄关键日期节点列表法：

司龄划分-2 = 
SUMX(
    '司龄划分表',
    VAR Start_ = '司龄划分表'[起始]
    VAR End_ = '司龄划分表'[结束]
    VAR Employee_ = 
        CALCULATETABLE(
            '人员表',
            '日期表'[Date]<=MAX('日期表'[Date]),
            '人员表'[离职日期]=BLANK() || '人员表'[离职日期] >= MIN('日期表'[Date])
        )
    VAR AddCompanyYear = 
        ADDCOLUMNS(
            Employee_,
            "司龄",
                VAR OnCompany_Start = '人员表'[入职日期]
                VAR OnCompany_End = MIN(COALESCE('人员表'[离职日期],MAX('日期表'[Date])),MAX('日期表'[Date]))
                VAR NodeDateList = ADDCOLUMNS(GENERATESERIES(0,200),"NodeDate",EDATE(OnCompany_Start,12*[Value]))
                RETURN
                MAXX(FILTER(NodeDateList,[NodeDate]<=OnCompany_End),[Value])
        )
    RETURN
    COUNTROWS(FILTER(AddCompanyYear,Start_<=[司龄] && [司龄]<=End_))
)

3、常规判断法：

司龄划分-3 = 
SUMX(
    '司龄划分表',
    VAR Start_ = '司龄划分表'[起始]
    VAR End_ = '司龄划分表'[结束]
    VAR Employee_ = 
        CALCULATETABLE(
            '人员表',
            '日期表'[Date]<=MAX('日期表'[Date]),
            '人员表'[离职日期]=BLANK() || '人员表'[离职日期] >= MIN('日期表'[Date])
        )
    VAR AddCompanyYear = 
        ADDCOLUMNS(
            Employee_,
            "司龄",
                VAR OnCompany_Start = '人员表'[入职日期]
                VAR OnCompany_End = MIN(COALESCE('人员表'[离职日期],MAX('日期表'[Date])),MAX('日期表'[Date]))
                VAR CompanyYear = YEAR(OnCompany_End)-YEAR(OnCompany_Start)
                RETURN
                IF(
                    OR(
                        MONTH(OnCompany_Start)>MONTH(OnCompany_End),
                        AND(
                            MONTH(OnCompany_Start)=MONTH(OnCompany_End),
                            DAY(OnCompany_Start)>DAY(OnCompany_End)
                        )
                    ),
                    CompanyYear-1,
                    CompanyYear
                )
        )
    RETURN
    COUNTROWS(FILTER(AddCompanyYear,Start_<=[司龄] && [司龄]<=End_))
)

最后创建一个矩阵，并将司龄作为行标签，年份作为列标签，再将上面的度量值放入矩阵的值字段即可，结果如下图所示：