DAX实现带条件的DATEDIFF平均值：Jira工单月度统计技术问询

基础准备 #

1. 日期表：必须创建并标记为日期表，需包含核心时间字段（DAX生成示例）：

Date = 
ADDCOLUMNS(
    CALENDAR(DATE(2020,1,1), TODAY()),
    "YearMonth", FORMAT([Date], "YYYY-MM"),
    "MonthStart", EOMONTH([Date], -1) + 1,
    "MonthEnd", EOMONTH([Date], 0)
)

标记为日期表后，通过YearMonth字段关联工单表，用于按月份聚合数据。

2. 工单表字段规范：确保JiraTickets表包含TicketID（唯一标识）、StartDate（创建日期）、EndDate（关闭日期，未关闭工单建议设为DATE(9999,12,31)而非空白，简化逻辑）。

核心度量值 #

1. 当月活跃工单数量 #

统计当月内处于活跃状态（创建于当月及之前，且关闭于当月及之后/未关闭）的工单总数：

当月活跃工单数量 = 
VAR 当前月起始 = MIN('Date'[MonthStart])
VAR 当前月结束 = MAX('Date'[MonthEnd])
RETURN
CALCULATE(
    DISTINCTCOUNT(JiraTickets[TicketID]),
    JiraTickets[StartDate] <= 当前月结束,
    JiraTickets[EndDate] >= 当前月起始
)

2. 当月平均存续时长 #

对每个活跃工单计算其截至当月末的存续月份数（如3月创建、4月关闭，3月计1个月，4月计2个月），再取平均值：

当月平均存续时长 = 
VAR 当前月结束 = MAX('Date'[MonthEnd])
VAR 活跃工单集合 = 
    CALCULATETABLE(
        ADDCOLUMNS(
            DISTINCT(JiraTickets[TicketID]),
            "@存续月数",
            VAR 工单创建日 = MAXX(FILTER(JiraTickets, JiraTickets[TicketID] = EARLIER(JiraTickets[TicketID])), JiraTickets[StartDate])
            VAR 实际结束日 = MIN(JiraTickets[EndDate], 当前月结束)
            RETURN DATEDIFF(工单创建日, 实际结束日, MONTH) + 1
        ),
        JiraTickets[StartDate] <= 当前月结束,
        JiraTickets[EndDate] >= MIN('Date'[MonthStart])
    )
RETURN
AVERAGEX(活跃工单集合, [@存续月数])

报表配置 #

1. 将日期表的YearMonth字段拖至X轴，设置为类别排序
2. 将两个度量值拖至Y轴，分别配置为柱状图（工单数量）和折线图（平均时长）
3. 若需筛选时间范围，可添加日期切片器关联日期表

性能优化建议 #

• 为JiraTickets表的TicketID、StartDate、EndDate字段创建列索引
• 统一未关闭工单的EndDate为远未来日期，避免空值判断带来的性能损耗
• 若数据量极大，可考虑在Power Query中预计算工单的月度存续记录，再结合DAX聚合（常规数据量下DAX方案已足够高效）

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