Power BI实现患者在手库存报表（含历史30天数据）

两种方式均可行，具体选择取决于数据规模与需求场景： #

1. 完全在Power BI服务中实现 #

可以直接用你提到的Power Query M语言逻辑完成，无需SQL端预处理：

• 操作步骤：
  • 导入原始表数据后，添加自定义列，定义每个库存记录的起始日期（提取CreatedDT的日期部分）和结束日期（若PickedForDate不为空则用该值，否则用报表统计的截止日，如当前日期或近30天的最后一天）。
  • 使用List.Dates([起始日期], Number.From([结束日期]-[起始日期])+1, #duration(1,0,0,0))生成该库存存续的所有日期序列。
  • 展开生成的日期列表，将单条库存记录拆分为存续期内的每日行。
  • 最后按PatientId和Date分组，汇总VolumeInMl得到每日在手库存。
• 适用场景：数据量较小（如单表记录数10万以内）、近30天统计需求简单、后续维度变更不频繁。

2. SQL Server端预处理 #

如果数据量较大，或后续要新增ProductId等维度，建议在SQL端完成日期展开与预处理：

• 示例SQL逻辑（递归CTE生成日期序列）：

WITH DateRange AS (
    -- 生成近30天的统计日期范围
    SELECT CAST(DATEADD(DAY, -29, GETDATE()) AS DATE) AS StatDate
    UNION ALL
    SELECT DATEADD(DAY, 1, StatDate)
    FROM DateRange
    WHERE StatDate < CAST(GETDATE() AS DATE)
),
BottleDates AS (
    -- 关联每个库存记录的存续日期
    SELECT 
        b.PatientId,
        dr.StatDate,
        b.VolumeInMl
    FROM [你的表名] b
    JOIN DateRange dr ON 
        dr.StatDate >= CAST(b.CreatedDT AS DATE)
        AND (dr.StatDate <= b.PickedForDate OR b.PickedForDate IS NULL)
)
-- 按患者和日期汇总库存
SELECT 
    PatientId,
    StatDate AS Date,
    SUM(VolumeInMl) AS volumeInStock
FROM BottleDates
GROUP BY PatientId, StatDate
ORDER BY PatientId, StatDate;

• 优势：SQL Server处理大规模数据的性能更优，预处理后Power BI只需加载汇总结果，减少前端计算压力，后续新增维度时扩展更灵活。

总结 #

• 小数据量、快速验证：直接在Power BI服务中用M语言实现。
• 大数据量、长期维护：优先选择SQL Server端预处理，提升报表性能与可扩展性。

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