PowerBI中通过DAX为Date_List表计算平均日期差的需求

现有数据表 #

1. Date_List表 #

2. Main表 #

需求说明 #

需要在Date_List表中创建计算列Avg_DateDiff_Days，针对每行的Date_List[Date]，计算Main表中符合以下条件的所有行的DATEDIFF(Main[Start Date], Date_List[Date], DAY)的平均值：

• Main[Start Date] < Date_List[Date]
• 任务在当前日期处于进行中：要么Main[End Date] > Date_List[Date]，要么Main[End Date]为空（即状态为On-going的任务）

问题分析 #

初始DAX公式未考虑Main[End Date]为空的情况，且直接使用AVERAGE配合行上下文迭代会导致计算逻辑错误，需要明确筛选符合条件的行后再计算平均值。

正确DAX公式 #

Avg_DateDiff_Days = 
VAR CurrentDate = Date_List[Date]
VAR EligibleRows = 
    FILTER(
        Main,
        Main[Start Date] < CurrentDate &&
        (ISBLANK(Main[End Date]) || Main[End Date] > CurrentDate)
    )
RETURN
    IF(
        COUNTROWS(EligibleRows) > 0,
        AVERAGEX(
            EligibleRows,
            DATEDIFF(Main[Start Date], CurrentDate, DAY)
        ),
        BLANK()
    )

公式解释 #

1. CurrentDate变量：存储当前行的Date_List[Date]值，简化后续重复引用
2. EligibleRows变量：筛选Main表中符合条件的行：
   • 任务开始日期早于当前日期
   • 任务结束日期为空（进行中）或晚于当前日期
3. 返回逻辑：如果有符合条件的行，计算这些行的日期差平均值；否则返回空值，避免无数据时出现错误

期望输出示例 #

注：Avg_DateDiff_Days的数值仅为示例，非真实结果。

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