员工第二年留存率多维数据集内置度量开发技术求助

需求明确 #

• 分母：过去24个月内入职的有效员工总数（含新员工标识，排除因[T-RETIRED]、[T-NOSTART]离职的员工）
• 分子：上述有效员工中仍在职的人数（无论是否调岗）
• 需将该比率设为多维数据集内置度量，同时解决原代码仅支持单日期筛选、日期范围下CurrentMember报错的问题

问题根源 #

原滚动24个月计算依赖CurrentMember，当WHERE子句传入日期集合时，CurrentMember无法指向集合元素，导致报错。需重构日期范围计算逻辑，并将所有度量逻辑整合到多维数据集脚本中，实现内置化。

解决方案 #

1. 多维数据集脚本：定义基础集合与滚动计算 #

替换原有SCOPE语句，使用更灵活的日期函数支持范围筛选：

-- 预先定义需排除的终止原因集合
CREATE SET [Termination Reasons].[Reason Code].[ExcludedReasons] AS 
  {[Termination Reasons].[Reason Code].&[T-RETIRED], [Termination Reasons].[Reason Code].&[T-NOSTART]};

-- 重构滚动24个月计算，支持单日期/日期范围
CREATE MEMBER [Date Calculations].[Calculation].[Rolling 24 Months] AS
  Aggregate(
    DATESBETWEEN(
      [Post Date].[Date Hierarchy].[Date],
      PARALLELPERIOD([Post Date].[Date Hierarchy].[Month], 23, [Post Date].[Date Hierarchy].CurrentMember),
      [Post Date].[Date Hierarchy].CurrentMember
    ),
    [Date Calculations].[Calculation].&[Actuals]
  );

2. 多维数据集脚本：创建内置计算度量 #

将原临时成员迁移到脚本中，实现内置度量：

-- 1. 计算符合条件的新员工总数（排除指定离职原因）
CREATE MEMBER [Measures].[NewHires_Valid] AS
  (
    [New Hire Types].[New Hire Type ID].&[New Hire],
    [Termination Reasons].[Reason Code].[All] - [Termination Reasons].[Reason Code].[ExcludedReasons],
    [Measures].[Employee Events Drill-Through]
  ),
  VISIBLE = 1;

-- 2. 计算仍在职的目标员工数
CREATE MEMBER [Measures].[RetainedEmployees_2ndYear] AS
  SUM(
    Filter(
      [Employees].[Employee ID].Children,
      -- 满足入职条件且未被排除
      (
        [New Hire Types].[New Hire Type ID].&[New Hire],
        [Termination Reasons].[Reason Code].[All] - [Termination Reasons].[Reason Code].[ExcludedReasons],
        [Measures].[Employee Events Drill-Through]
      ) >= 1
      -- 满足当前在职条件（需确认Event Type &[1]为在职标识）
      AND (
        [Event Types].[Event Type].&[1],
        [Measures].[Employee Events Drill-Through]
      ) >= 1
    ),
    1
  ),
  VISIBLE = 1;

-- 3. 最终第二年留存率度量
CREATE MEMBER [Measures].[2ndYearRetention] AS
  Divide(
    [Measures].[RetainedEmployees_2ndYear],
    [Measures].[NewHires_Valid],
    0
  ),
  FORMAT_STRING = 'Percent',
  VISIBLE = 1;

3. 关键优化说明 #

• 使用[All] - [ExcludedReasons]直接排除无效离职原因，替代原聚合成员，逻辑更简洁
• 用SUM(Filter(...),1)统计员工数量，避免Aggregate带来的上下文冲突
• 滚动计算改用DATESBETWEEN+PARALLELPERIOD，支持任意日期范围筛选，解决原CurrentMember报错问题

4. 查询使用示例 #

部署脚本后，可直接调用内置度量：

SELECT 
  {
    [Measures].[RetainedEmployees_2ndYear],
    [Measures].[NewHires_Valid],
    [Measures].[2ndYearRetention]
  } ON COLUMNS,
  [HR Organizational Units].[Entity].Children ON ROWS
FROM [Employee Events]
WHERE 
  (
    [Date Calculations].[Calculation].[Rolling 24 Months],
    {[Post Date].[Date Hierarchy].[FY 2022-Apr]:[Post Date].[Date Hierarchy].[FY 2023-Mar]} -- 日期范围示例
  );

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