枫言枫语员工司龄分析是企业人力资源管理中的重要环节，它不仅能帮助企业了解员工队伍的稳定性，还能为人才保留、薪酬福利设计等决策提供数据支持。本篇文章将介绍如何在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_))
)最后创建一个矩阵，并将司龄作为行标签，年份作为列标签，再将上面的度量值放入矩阵的值字段即可，结果如下图所示：
总结
以上方法仅供参考，若有更优雅的解决方案，欢迎留言讨论，或者加入我们的技术交流群，一起享受这种思维碰撞的快乐吧！
PBI/DAX技术交流群(QQ)：344353627
