如何用SQL计算每日问题平均开放天数并在PowerBI制作时间维度图表？

你要的是在指定日期范围内，每个时间点（比如每天）所有处于开放状态的问题的平均已开放天数，和你最初写的单个问题总开放天数查询逻辑不一样，下面给你两种可行的实现方式：

方式一：在数据源端用SQL预处理数据 #

如果希望直接从数据库获取计算好的结果再导入PowerBI，可以用CTE生成日期序列，再关联Issues表计算每个日期的平均值：

WITH DateRange AS (
    -- 生成覆盖所有需要分析的日期范围：从最早创建日期到最晚关闭日期（或当前日期）
    SELECT MIN(created) AS DateValue FROM Issues
    UNION ALL
    SELECT DATEADD(DAY, 1, DateValue)
    FROM DateRange
    WHERE DateValue < (SELECT MAX(ISNULL(closed, GETDATE())) FROM Issues)
)
SELECT 
    dr.DateValue AS [Date],
    AVG(DATEDIFF(DAY, i.created, dr.DateValue)) AS AvgDaysOpen
FROM DateRange dr
LEFT JOIN Issues i 
    ON i.created <= dr.DateValue 
    AND (i.closed >= dr.DateValue OR i.closed IS NULL)
GROUP BY dr.DateValue
ORDER BY dr.DateValue
OPTION (MAXRECURSION 0); -- 若日期范围超过100天必须添加此选项

逻辑说明： #

• DateRange CTE递归生成了所有需要分析的日期，确保每个时间点都被覆盖
• 关联条件筛选出在当前日期处于开放状态的问题：创建日期≤当前日期，且关闭日期≥当前日期（或未关闭）
• 对每个日期，计算所有开放问题的已开放天数的平均值

方式二：在PowerBI中用DAX动态计算（更灵活） #

如果需要在PowerBI里自由调整日期范围，推荐用DAX创建度量值，步骤如下：

1. 先创建日期表 #

在PowerBI的「建模」选项卡点击「新建表」，输入以下DAX生成覆盖所有需要的日期：

Calendar = CALENDAR(
    MIN(Issues[created]), 
    MAX(IF(Issues[closed] <> BLANK(), Issues[closed], TODAY()))
)

2. 创建平均开放天数度量值 #

同样在「建模」选项卡点击「新建度量值」，输入：

Avg Days Open = 
VAR CurrentDate = MAX(Calendar[Date])
-- 筛选出当前日期处于开放状态的问题
VAR OpenIssues = 
    FILTER(
        Issues,
        Issues[created] <= CurrentDate &&
        (Issues[closed] >= CurrentDate || ISBLANK(Issues[closed]))
    )
-- 计算所有开放问题的已开放天数总和
VAR TotalOpenDays = SUMX(OpenIssues, DATEDIFF(Issues[created], CurrentDate, DAY))
-- 统计开放问题数量
VAR OpenIssueCount = COUNTROWS(OpenIssues)
-- 返回平均值，无开放问题时返回空白
RETURN
    IF(OpenIssueCount > 0, TotalOpenDays / OpenIssueCount, BLANK())

3. 创建图表 #

把Calendar[Date]拖到图表的X轴，把Avg Days Open度量值拖到Y轴，选择折线图或柱形图即可。你可以通过切片器筛选任意日期范围，图表会自动更新平均值。

逻辑说明： #

• 日期表是时间序列分析的基础，确保每个时间点都能被单独计算
• 度量值会针对图表上的每个日期，动态筛选当时开放的问题，计算它们的已开放天数平均值
• 处理了无开放问题的场景，避免出现0值干扰图表展示

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