如何在Power BI中创建显示本周服务年限里程碑的长期服务列？

一、实现逻辑 #

要找出本周内达到5、10、15…70年服务里程碑的同事，需同时满足两个核心条件：

• 入职至今的完整年数是5的倍数
• 对应的周年纪念日落在当前周范围内

二、Excel公式列设置 #

假设原始表结构如下：

• A列：同事姓名（数据从A2行开始）
• B列：入职日期（数据从B2行开始）

1. 添加「是否符合条件」列（C列） #

在C2单元格输入以下公式，下拉填充至所有数据行：

=AND(MOD(DATEDIF(B2,TODAY(),"y"),5)=0,WEEKNUM(EDATE(B2,DATEDIF(B2,TODAY(),"y")*12),2)=WEEKNUM(TODAY(),2))

公式细节说明：

• DATEDIF(B2,TODAY(),"y")：计算入职到当前日期的完整年数
• MOD(...,5)=0：判断年数是否为5的倍数（自动匹配5、10…70年的里程碑）
• EDATE(B2,年数*12)：精准计算周年纪念日，自动适配闰年带来的日期偏差
• WEEKNUM(...,2)：将日期转换为周数，参数2表示以周一作为一周的起始日
• AND(...)：确保「年数符合里程碑」和「纪念日在本周」两个条件同时成立

2. 添加「服务年限」列（D列） #

在D2单元格输入公式，下拉填充：

=IF(C2,DATEDIF(B2,TODAY(),"y")&"年","")

该列会自动为符合条件的同事显示对应服务年限，不符合条件的单元格留空。

3. 生成目标列表 #

筛选C列值为TRUE的行，即可得到本周达成服务里程碑的同事名单。

三、示例验证 #

针对你提供的示例数据：

• Joe Bloggs（入职日期25/10/2017）：2022年10月25日为入职5周年，若当前日期处于该周内，公式会标记为符合条件，显示「5年」
• Jane Doe（入职日期23/10/2002）：2022年10月23日为入职20周年，若当前日期处于该周内，公式会标记为符合条件，显示「20年」

四、自定义调整 #

• 若你的周起始日不是周一，修改WEEKNUM函数的第二个参数即可：比如参数1代表以周日作为一周起始日
• 若后续需要扩展里程碑范围（如75年），无需修改公式，只要年数为5的倍数会自动识别

内容的提问来源于stack exchange，提问作者Pompeygeorge