Power BI DAX：筛选ID非连续月度记录及计算连续达标时长

核心逻辑 #

要筛选并计算连续达标月度，关键是通过窗口函数标记连续月份组，再对每组统计时长。以下是基于SQL的实操步骤（适配大多数关系型数据库如PostgreSQL、BigQuery、MySQL 8.0+）：

步骤1：预处理数据（计算月度达标记录） #

如果原始数据是员工各项目的工时明细，首先需要按ID+月度聚合，计算整体内部工时占比，筛选出≥80%的月份：

WITH monthly_ratio AS (
    SELECT
        id,
        -- 将日期截断到当月月初，统一月度格式
        DATE_TRUNC(date, MONTH) AS month_year,
        -- 计算月度内部工时占比
        ROUND(SUM(internal_hours) / SUM(internal_hours + external_hours) * 100, 2) AS internal_ratio
    FROM main_table
    GROUP BY id, DATE_TRUNC(date, MONTH)
),
-- 筛选出达标月份
qualified_months AS (
    SELECT id, month_year
    FROM monthly_ratio
    WHERE internal_ratio >= 80
)

步骤2：标记连续月度分组 #

通过窗口函数计算每个达标月份与上一个达标月份的间隔，标记出连续的月份组：

, ranked_months AS (
    SELECT
        id,
        month_year,
        -- 计算当前月与上一个达标月的月份差
        DATE_DIFF(month_year, LAG(month_year) OVER (PARTITION BY id ORDER BY month_year), MONTH) AS month_gap
    FROM qualified_months
),
grouped_continuous AS (
    SELECT
        id,
        month_year,
        -- 累加不连续的节点，生成连续组ID
        SUM(CASE WHEN month_gap = 1 OR month_gap IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY id ORDER BY month_year) AS continuous_group
    FROM ranked_months
)

• LAG()：获取当前ID的上一个达标月份
• month_gap：如果等于1，说明和上月连续；如果为NULL，是该ID的第一个达标月；大于1则表示断档
• continuous_group：同一连续序列的月份会被分配相同的组ID

步骤3：计算连续时长（截至上月） #

统计每个连续组的月份数，过滤掉当前月（如果需要截至上月），最终取每个ID的最长/最近连续时长：

, group_stats AS (
    SELECT
        id,
        continuous_group,
        COUNT(*) AS continuous_months,
        MAX(month_year) AS group_end_month
    FROM grouped_continuous
    -- 仅保留截至上月的分组（可选，根据需求调整）
    WHERE month_year < DATE_TRUNC(CURRENT_DATE(), MONTH)
    GROUP BY id, continuous_group
)
-- 取每个ID的最大连续时长（如果要最近的连续组，可改为按group_end_month降序取第一条）
SELECT
    id,
    MAX(continuous_months) AS max_continuous_months
FROM group_stats
GROUP BY id;

示例验证 #

对应你给出的例子：

• ID1：达标月为2、3月 → month_gap=1，同一组，统计得2个月
• ID2：仅3月达标 → 单组，统计得1个月
• ID3：达标月为12月、2月、3月 → 12月到2月month_gap=2（断档），分为两组，其中2、3月组为2个月，取最大值即为2

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