如何自动化SQL中用于Power BI月度报表的CASE WHEN周划分语句？

我协助团队完成月度报表制作，现在需要优化这段CASE WHEN代码，实现自动化，避免每次做报表都手动新增分支。这段代码用于Power BI拉取数据，其中第1周包含8天，其余每周都是7天。原SQL代码如下：

SELECT 
    CASE
        WHEN DATE BETWEEN '2023-05-24' AND '2023-05-31' THEN 'Week 1'
        WHEN DATE BETWEEN '2023-06-01' AND '2023-06-07' THEN 'Week 2'
        WHEN DATE BETWEEN '2023-06-08' AND '2023-06-14' THEN 'Week 3'
        WHEN DATE BETWEEN '2023-06-15' AND '2023-06-21' THEN 'Week 4'
        WHEN DATE BETWEEN '2023-06-22' AND '2023-06-28' THEN 'Week 5'
        COUNT(ACCOUNT_NUMBER)
        ELSE 'other' 
    END AS Account_open_week
FROM
    acct

首先修正原SQL的语法错误：COUNT(ACCOUNT_NUMBER)不能放在CASE分支里，正确逻辑是先按周分组，再执行聚合计数。下面提供两种自动化实现方式：

方式一：SQL层面动态计算周数 #

基于你的周起始规则（第1周从2023-05-24开始，第1周8天，之后每周7天），用日期差计算动态生成周数：

SELECT
    CASE
        WHEN DATE < '2023-05-24' THEN 'other'
        WHEN DATE <= '2023-05-31' THEN 'Week 1'
        ELSE CONCAT('Week ', FLOOR(DATEDIFF(DAY, '2023-05-31', DATE)/7) + 2)
    END AS Account_open_week,
    COUNT(ACCOUNT_NUMBER) AS account_count
FROM acct
GROUP BY
    CASE
        WHEN DATE < '2023-05-24' THEN 'other'
        WHEN DATE <= '2023-05-31' THEN 'Week 1'
        ELSE CONCAT('Week ', FLOOR(DATEDIFF(DAY, '2023-05-31', DATE)/7) + 2)
    END

• 逻辑说明：
  1. 日期早于第1周起始日，直接归为other
  2. 日期在第1周范围内，标记为Week 1
  3. 后续日期计算与2023-05-31的天数差，除以7取整后加2，得到对应周数（比如2023-06-01与2023-05-31差1天，1/7取整为0，加2得到Week 2）
• 适配不同月度时，仅需修改2023-05-24（起始日）和2023-05-31（第1周截止日）两个参数，无需新增CASE分支。

方式二：Power BI中用DAX处理（更灵活） #

如果报表需要频繁切换月度，建议在Power BI中加载原始数据后，通过DAX计算列生成周标签：

1. 将acct表导入Power BI
2. 添加计算列，DAX公式如下：

Account_open_week = 
VAR StartDate = DATE(2023,5,24)
VAR Week1End = DATE(2023,5,31)
VAR DateDiff = DATEDIFF(Week1End, acct[DATE], DAY)
RETURN
    IF(acct[DATE] < StartDate, "other",
        IF(acct[DATE] <= Week1End, "Week 1",
            "Week " & INT(DateDiff/7) + 2
        )
    )

3. 后续直接在Power BI中创建可视化，按Account_open_week分组计数即可，无需修改SQL。

这种方式可通过Power BI的参数面板动态调整StartDate和Week1End，完全实现自动化，不用每次修改代码。

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