PowerBI技术咨询：如何返回选定月份内处于活跃状态的所有订阅记录

核心判断逻辑 #

一个订阅在指定月份处于活跃状态，需满足订阅开始日期 ≤ 该月份最后一天且订阅结束日期 ≥ 该月份第一天，即订阅周期与目标月份存在时间重叠。

1. SQL直接查询方案 #

如果需要通过SQL直接获取指定月份的活跃订阅记录，可以通过日期表关联订阅表，利用日期范围判断实现：

SELECT DISTINCT s.*
FROM Subscription s
JOIN DateTable dt 
  ON s.[Start Date] <= EOMONTH(dt.Date)
  AND s.[End Date] >= DATEFROMPARTS(dt.Year, dt.Month, 1)
WHERE dt.Year = 2023 
  AND dt.Month = 2; -- 替换为目标年月

• 用EOMONTH(dt.Date)获取目标月份的最后一天，DATEFROMPARTS生成目标月份的第一天；
• DISTINCT用于去重，因为日期表中每个日期都会匹配一次订阅记录；
• 替换WHERE条件中的年月参数，即可查询任意月份的活跃订阅。

2. BI可视化工具适配方案（以Power BI为例） #

如果是在可视化组件中实现动态筛选（比如选择年月后自动展示活跃数量），可以通过度量值实现，无需修改现有表关联：

活跃订阅数量 = 
VAR 选中月份开始 = STARTOFMONTH(DateTable[Date])
VAR 选中月份结束 = ENDOFMONTH(DateTable[Date])
RETURN
CALCULATE(
    DISTINCTCOUNT(Subscription[ID]),
    Subscription[Start Date] <= 选中月份结束,
    Subscription[End Date] >= 选中月份开始
)

• 当在可视化界面选择日期表中的年月时，度量值会自动计算该月份的活跃订阅数；
• 如果需要展示具体订阅记录，可在可视化组件的筛选器中添加两个条件：
  • Subscription[Start Date] ≤ 选中月份最后一天
  • Subscription[End Date] ≥ 选中月份第一天

3. 预生成月度关联表方案 #

如果需要频繁按月份统计活跃订阅，可以预先生成订阅与对应活跃月份的关联表，后续查询效率更高：

SELECT 
    s.ID,
    s.[Start Date],
    s.[End Date],
    dt.Year,
    dt.Month,
    dt.[Year-Month]
FROM Subscription s
JOIN DateTable dt 
  ON dt.Date BETWEEN s.[Start Date] AND s.[End Date]
GROUP BY s.ID, s.[Start Date], s.[End Date], dt.Year, dt.Month, dt.[Year-Month];

• 该SQL会为每个订阅生成其覆盖的所有月度记录，比如ID=1的订阅会生成2023年1月、2023年2月两条记录；
• 后续只需按Year-Month筛选，就能直接得到对应月份的所有活跃订阅。

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