基于DirectQuery创建自定义列计算不含周五周六的日期差值

在DirectQuery模式下，迭代类DAX函数（如SUMX）会受限制，我们可以通过数学计算的方式实现需求，以下是可用的DAX自定义列公式：

工作日差值 = 
VAR 总天数 = DATEDIFF('你的表名'[起始日期], '你的表名'[结束日期], DAY) + 1 // 包含起始日和结束日的总天数
VAR 起始周几 = WEEKDAY('你的表名'[起始日期], 2) // 1=周一，2=周二...5=周五，6=周六，7=周日
VAR 结束周几 = WEEKDAY('你的表名'[结束日期], 2)
VAR 完整周数 = INT(总天数 / 7)
VAR 剩余天数 = MOD(总天数, 7)
VAR 剩余周末数 = 
    SWITCH(
        TRUE(),
        // 剩余天数区间完全在工作日（周一至周四），无周末
        起始周几 + 剩余天数 - 1 < 5, 0,
        // 剩余天数跨工作日和周末（起始在工作日，结束在周末）
        起始周几 <= 5 && 结束周几 >= 6, MIN(剩余天数, 6 - 起始周几 + 1) + MAX(0, 结束周几 - 5),
        // 起始日本身是周末，计算剩余天数内的周末数
        起始周几 > 5, MIN(剩余天数, 7 - 起始周几 + 1),
        // 起始在工作日，剩余天数覆盖到周末（仅周六或周五+周六）
        MAX(0, 起始周几 + 剩余天数 - 5)
    )
VAR 总周末数 = 完整周数 * 2 + 剩余周末数
RETURN
    // 处理起始日期晚于结束日期的情况，返回负数差值；正常情况返回总天数减周末数
    IF('你的表名'[起始日期] > '你的表名'[结束日期], 
        -(总天数 - 总周末数), 
        总天数 - 总周末数
    )

关键说明： #

• WEEKDAY参数2：将周一设为1，周日设为7，方便直接对应周五（5）、周六（6）这两个需要排除的日期。
• 总天数计算：加1是为了包含起始日和结束日，比如2024-05-01到2024-05-02的总天数是2天。
• 剩余周末数逻辑：通过SWITCH分情况计算完整周之外的剩余天数里包含的周五、周六数量，覆盖所有日期区间场景。
• DirectQuery兼容性：所有用到的DAX函数均支持DirectQuery模式，不会触发不兼容报错。

使用时只需将公式中的你的表名、起始日期、结束日期替换为你实际的表和列名即可。

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