Power BI如何按日期区间校验员工项目分配占比是否超100%

你可以直接使用以下CSV数据做测试：

ID,Project,From,To,Percentage
1,APPLE,01-01-2022,31-03-2022,50
1,MICROSOFT,01-01-2022,15-01-2022,50
1,MICROSOFT,01-02-2022,28-02-2022,50
1,MICROSOFT,01-03-2022,31-03-2022,50
2,ORACLE,01-02-2022,23-06-2022,50
3,APPLE,23-04-2022,23-06-2022,100
1,MICROSOFT,16-01-2022,31-01-2022,50
2,DELL,01-12-2021,01-04-2022,50

注意提前将From、To列转换为日期格式，避免日期比较逻辑出错。

你之前使用的DAX计算列逻辑存在缺陷：只要记录和当前行时间段存在任意重叠，就直接累加对应Percentage值，完全没有区分重叠范围的实际覆盖区间，会把同一ID下非同时段的分配占比重复累加。比如ID=1的APPLE项目行，会被判定和4条MICROSOFT记录都有重叠，最终算出250%的错误结果，无法反映真实的同时段分配占比。

核心思路是先将同一员工的所有项目时间边界拆分为最小粒度的不重叠时间切片，保证每个切片内生效的项目分配组合固定，再逐切片统计占比总和，判断是否存在超过100%的情况。

步骤1：创建连续日期表（可选） #

如果数据量不大，逐天统计的逻辑更简单，可先创建覆盖所有项目起止范围的连续日期表：

DimDate = 
CALENDAR(
    MINX('sample set', 'sample set'[From]),
    MAXX('sample set', 'sample set'[To])
)

该表无需和事实表建立关系。

步骤2：创建超配判断计算列 #

以下计算列可以直接判断当前行所属员工是否存在任意时段分配占比超过100%的情况，返回TRUE代表存在超配，FALSE代表无超配：

HasOverAllocation = 
VAR CurrEmpID = 'sample set'[ID]
-- 取出当前员工所有分配记录
VAR EmpRecords = FILTER(ALL('sample set'), 'sample set'[ID] = CurrEmpID)
-- 提取所有时间切点：每个项目的开始日期、结束日期次日，避免端点重复计算
VAR DateBreakpoints = 
    DISTINCT(
        UNION(
            SELECTCOLUMNS(EmpRecords, "Breakpoint", 'sample set'[From]),
            SELECTCOLUMNS(EmpRecords, "Breakpoint", 'sample set'[To] + 1)
        )
    )
-- 为切点排序，生成连续不重叠的最小时间切片
VAR RankedBreakpoints = ADDCOLUMNS(DateBreakpoints, "Rank", RANKX(DateBreakpoints, [Breakpoint], , ASC, DENSE))
VAR TimeSlices = 
    FILTER(
        ADDCOLUMNS(
            NATURALLEFTOUTERJOIN(
                SELECTCOLUMNS(RankedBreakpoints, "SliceStart", [Breakpoint], "CurrRank", [Rank]),
                SELECTCOLUMNS(RankedBreakpoints, "NextBreakpoint", [Breakpoint], "NextRank", [Rank])
            ),
            "SliceEnd", [NextBreakpoint] - 1
        ),
        [CurrRank] = [NextRank] - 1
    )
-- 逐切片计算同时段总分配占比
VAR SliceTotalPct = 
    ADDCOLUMNS(
        TimeSlices,
        "TotalAllocation",
            CALCULATE(
                SUM('sample set'[Percentage]),
                FILTER(
                    EmpRecords,
                    'sample set'[From] <= [SliceStart] && 'sample set'[To] >= [SliceEnd]
                )
            )
    )
-- 判断是否存在超100%的切片
VAR OverAllocExist = COUNTROWS(FILTER(SliceTotalPct, [TotalAllocation] > 100)) > 0
RETURN OverAllocExist

结果验证 #

用你提供的测试数据运行后：

• ID=1的所有行返回FALSE：APPLE项目50%占比覆盖全周期，MICROSOFT项目的分段50%占比刚好和苹果的时段拼接，任意时段总和为100%，无超配
• ID=2的所有行返回FALSE：DELL和ORACLE项目重叠时段占比总和为100%，其余时段单项目占比50%，无超配
• ID=3的所有行返回FALSE：单项目100%占比，无超配

如果需要做全局校验，只需要将上述逻辑调整为度量值，遍历所有ID判断即可，不需要逐行加计算列。

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