PowerBI员工缺勤统计求助：按日期计算每日缺勤人数

需求说明 #

制作员工缺勤仪表板，统计指定日期的员工缺勤人数，生成每日缺勤人数统计表，缺勤需覆盖起始日、结束日及中间日期。

数据示例 #

Full Name   Start Date  End Date
----------------------------------
Employee D  03/11/2022  05/11/2022
Employee E  03/11/2022  04/11/2022
Employee A  04/11/2022  04/11/2022
Employee B  04/11/2022  06/11/2022
Employee C  04/11/2022  04/11/2022
Employee B  05/11/2022  06/11/2022

期望结果 #

Date       Count
----------------    
03/11/2022   2
04/11/2022   5
05/11/2022   3
06/11/2022   2

原公式问题 #

你使用的公式逻辑错误，导致统计结果不准确：

Count per day = COUNTROWS(FILTER('Staff absence', 'Staff absence'[Absence Start Date]= MIN('Attendance Dates'[Date]) && 'Staff absence'[Absence End Date] >= MAX('Attendance Dates'[Date])))

问题点：

• 使用MIN('Attendance Dates'[Date])和MAX('Attendance Dates'[Date])会取整个日期表的首尾日期，而非当前行要统计的单日日期
• 条件逻辑错误，未判断当前日期是否处于员工的缺勤区间内

修正后的公式 #

方案1：FILTER+COUNTROWS #

Count per day = 
COUNTROWS(
    FILTER(
        'Staff absence',
        'Staff absence'[Absence Start Date] <= SELECTEDVALUE('Attendance Dates'[Date]) &&
        'Staff absence'[Absence End Date] >= SELECTEDVALUE('Attendance Dates'[Date])
    )
)

方案2：CALCULATE（性能更优） #

Count per day = 
CALCULATE(
    COUNTROWS('Staff absence'),
    'Staff absence'[Absence Start Date] <= SELECTEDVALUE('Attendance Dates'[Date]),
    'Staff absence'[Absence End Date] >= SELECTEDVALUE('Attendance Dates'[Date])
)

逻辑说明 #

公式核心是判断当前统计日期（通过SELECTEDVALUE('Attendance Dates'[Date])获取）是否落在员工的缺勤起止区间内（开始日期≤当前日期≤结束日期），符合条件的缺勤记录将被计入当日统计数。

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