求基于门店周度Like-for-like对比的DAX替代实现方案

需求：基于周维度对门店业绩做Like-for-like同比对标，查询2023和2024年对应周的销售额，若任意一年该周销售额为0，则标记门店为“Close”，否则标记为“Open”。

以下是几种更简洁高效的替代实现方案：

方法一：利用时间智能函数简化同比计算 #

通过SAMEPERIODLASTYEAR自动匹配去年同期周，结合ALLEXCEPT简化筛选上下文，减少冗余代码：

Sales Status = 
VAR CurrentWeekSales = 
    CALCULATE(
        SUM(Overall[QTY]),
        ALLEXCEPT(Overall, Overall[Store Name New], Overall[Week])
    )
VAR LastYearWeekSales = 
    CALCULATE(
        SUM(Overall[QTY]),
        ALLEXCEPT(Overall, Overall[Store Name New]),
        SAMEPERIODLASTYEAR(Overall[Week])
    )
RETURN
    IF(
        (CurrentWeekSales = 0 && LastYearWeekSales > 0) || (CurrentWeekSales > 0 && LastYearWeekSales = 0),
        "Close",
        "Open"
    )

优化说明：

• ALLEXCEPT保留门店和周的筛选上下文，无需手动编写重复的相等判断
• SAMEPERIODLASTYEAR自动定位去年同期周，避免手动计算年份，逻辑更直观

方法二：用记录存在性判断替代销售额求和（适用于无销售即无数据行场景） #

如果门店某周无销售时，表中没有对应数据行，用COUNTROWS判断是否有销售记录，性能更优：

Sales Status = 
VAR HasCurrentWeekSales = 
    CALCULATE(
        COUNTROWS(Overall),
        ALLEXCEPT(Overall, Overall[Store Name New], Overall[Week])
    ) > 0
VAR HasLastYearWeekSales = 
    CALCULATE(
        COUNTROWS(Overall),
        ALLEXCEPT(Overall, Overall[Store Name New]),
        SAMEPERIODLASTYEAR(Overall[Week])
    ) > 0
RETURN
    IF(
        XOR(HasCurrentWeekSales, HasLastYearWeekSales),
        "Close",
        "Open"
    )

优化说明：

• COUNTROWS直接判断销售记录是否存在，比SUM更高效（尤其数据量较大时）
• XOR逻辑替代复杂的OR条件，清晰表达"一真一假"的判断需求

方法三：明确周维度匹配，避免跨年周歧义 #

通过WEEKNUM强制匹配对应周数，同时简化变量定义，提升代码可读性：

Sales Status = 
VAR CurrentStore = MAX(Overall[Store Name New])
VAR CurrentWeekDate = MAX(Overall[Week])
VAR CurrentYearSales = 
    CALCULATE(
        SUM(Overall[QTY]),
        Overall[Store Name New] = CurrentStore,
        YEAR(Overall[Week]) = YEAR(CurrentWeekDate)
    )
VAR LastYearSales = 
    CALCULATE(
        SUM(Overall[QTY]),
        Overall[Store Name New] = CurrentStore,
        YEAR(Overall[Week]) = YEAR(CurrentWeekDate) - 1,
        WEEKNUM(Overall[Week]) = WEEKNUM(CurrentWeekDate)
    )
RETURN
    IF(
        NOT(CurrentYearSales > 0 && LastYearSales > 0) && NOT(CurrentYearSales = 0 && LastYearSales = 0),
        "Close",
        "Open"
    )

优化说明：

• 提前定义常用变量，避免重复调用聚合函数
• 用WEEKNUM明确匹配周数，解决跨年周的对标歧义问题

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