Power BI中未结工单平均天数的移动平均计算显示空白求助

原代码情况 #

已实现正常运行的未结工单平均待办天数度量值：

Average Days open = 
CALCULATE(AVERAGEX('cases',IF('Cases'[Case Status]<>"Closed",DATEDIFF('Cases'[Created On],TODAY(), DAY))))

尝试创建12个月移动平均度量值时返回空白：

MA Avg Days open for open cases = 
VAR NumOfMonths = 12
VAR LastCurrentDate = MAX('Date'[Date])
VAR Period =
    DATESINPERIOD ( 'Date'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
    CALCULATE([Average Days open],  Period)
VAR FirstDateInPeriod = MINX ( Period, 'Date'[Date] )
VAR LastDateWithDate = MAX ( 'Date'[Date] )
RETURN
    IF ( FirstDateInPeriod <= LastDateWithDate, Result )

问题根源 #

1. 基础度量值硬绑定当前日期：原Average Days open使用TODAY()固定计算到当前日期的天数，无法响应移动平均逻辑中Period指定的过去12个月时间窗口筛选，导致筛选上下文完全失效。
2. 移动平均计算逻辑错误：直接用CALCULATE([Average Days open], Period)是将整个12个月的筛选应用到基础度量值，但基础度量值没有按时间粒度（如月份）拆分聚合，无法生成移动平均所需的逐期数据。

修正方案 #

步骤1：调整基础度量值，适配时间上下文 #

修改基础度量值，使其能根据当前筛选的日期计算对应时点的未结工单平均天数：

Average Days open (Time-Aware) = 
CALCULATE(
    AVERAGEX(
        'Cases',
        IF(
            'Cases'[Case Status] <> "Closed" && 'Cases'[Created On] <= MAX('Date'[Date]),
            DATEDIFF('Cases'[Created On], MAX('Date'[Date]), DAY)
        )
    )
)

• 用MAX('Date'[Date])替换TODAY()，适配当前筛选的日期上下文
• 增加'Cases'[Created On] <= MAX('Date'[Date])，确保只统计当前时点前创建的工单

步骤2：重构移动平均度量值 #

基于时间感知的基础度量值，按时间粒度（如年月）计算12个月移动平均：

MA Avg Days open for open cases = 
VAR NumOfMonths = 12
VAR LastCurrentDate = MIN(MAX('Date'[Date]), TODAY()) // 限制不超过当前实际日期
VAR Period = DATESINPERIOD('Date'[Date], LastCurrentDate, -NumOfMonths, MONTH)
VAR Result = 
    AVERAGEX(
        VALUES('Date'[Year Month]), // 按年月粒度聚合，可根据需求调整为日/周
        [Average Days open (Time-Aware)]
    )
VAR FirstDateInPeriod = MINX(Period, 'Date'[Date])
RETURN
    IF(FirstDateInPeriod <= LastCurrentDate, Result)

• 用AVERAGEX遍历每个时间粒度的平均天数，再计算移动平均
• 增加MIN(MAX('Date'[Date]), TODAY())避免计算未来日期的无效结果

额外注意事项 #

确保Cases表与Date表已通过Created On字段建立正确的关系（一对一或一对多），否则时间筛选无法生效。

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