DAX实现选中Q1时与上年Q4对比的未下单客户查询

你当前代码的问题是选中Q1时，LastQuarter会变成0，且仍用当前年份筛选，自然找不到匹配的数据。要实现Q1自动对比上年Q4的需求，只需调整上一季度的年份和季度值判断逻辑，修改后的完整代码如下：

Customers Not ordered This Quarter = 
VAR ThisQuarter =
    SELECTEDVALUE( DailyReport[DateCreated].[QuarterNo])
VAR ThisYEAR =
    SELECTEDVALUE(DailyReport[DateCreated].[Year])
VAR SelectedSupplier = 
    SELECTEDVALUE(DailyReport[SupplierName])
-- 新增逻辑：判断上一年和上一季度
VAR LastYear = 
    IF(ThisQuarter = 1, ThisYEAR - 1, ThisYEAR)
VAR LastQuarter = 
    IF(ThisQuarter = 1, 4, ThisQuarter - 1)
VAR CustomersThisQuarter =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER( ALL( DailyReport ), DailyReport[DateCreated].[QuarterNo] = ThisQuarter && DailyReport[SupplierName] = SelectedSupplier && DailyReport[DateCreated].[Year] = ThisYEAR),
            "C1", DailyReport[VenueName]
        )
    )
VAR CustomersLastQuarter =
    DISTINCT(
        SELECTCOLUMNS(
            -- 这里改用LastYear和LastQuarter筛选
            FILTER( ALL( DailyReport ), DailyReport[DateCreated].[QuarterNo] = LastQuarter && DailyReport[SupplierName] = SelectedSupplier && DailyReport[DateCreated].[Year] = LastYear),
            "C1", DailyReport[VenueName]
        )
    )
VAR T1 =
    EXCEPT(CustomersLastQuarter, CustomersThisQuarter )
RETURN
    CONCATENATEX( T1, [C1], UNICHAR(10), [C1], ASC)

关键修改说明 #

• LastYear变量：当当前季度是Q1时，上一年取当前年份减1；其他季度则和当前年份保持一致
• LastQuarter变量：Q1对应的上一季度固定为4（即上年的Q4），其他季度按原逻辑取当前季度减1
• CustomersLastQuarter筛选条件：替换原有的当前年份，改用新的LastYear变量，确保Q1时能正确匹配到上年Q4的数据

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