Power BI库存报表补全每日库存：SQL/DAX实现方案求助

完全可以用DAX实现无变动日期的库存补全，核心思路是先生成完整的日期-物品-地点组合，再通过上下文筛选获取每个组合最近一次的库存变动值并继承。以下是具体实现步骤：

1. 生成基础维度表 #

首先需要两个基础表来构建完整的日期-物品-地点矩阵：

• 日期表：覆盖原库存表的所有日期范围

  DateTable = CALENDAR(MIN('Item Ledger Entry'[Posting Date]), MAX('Item Ledger Entry'[Posting Date]))
  

• 物品-地点维度表：提取原表中所有不重复的物品+地点组合

  ItemLocation = DISTINCT(SELECTCOLUMNS('Item Ledger Entry', "Item No.", 'Item Ledger Entry'[Item No.], "Location Code", 'Item Ledger Entry'[Location Code]))
  

2. 创建完整的日期-物品-地点组合表 #

通过交叉连接生成所有可能的日期-物品-地点组合，确保无变动日期也有对应的记录：

FullInventoryDates = CROSSJOIN('DateTable', 'ItemLocation')

3. 计算每日库存值（度量值） #

创建DAX度量值来计算每个组合的当日库存，逻辑是找到该物品+地点在当日及之前的最后一次变动，取累计库存值：

Current Inventory = 
VAR CurrentDate = MAX('FullInventoryDates'[Date])
VAR LastEntryDate = 
    CALCULATE(
        MAX('Item Ledger Entry'[Posting Date]),
        FILTER(
            ALL('Item Ledger Entry'),
            'Item Ledger Entry'[Item No.] = MAX('FullInventoryDates'[Item No.]) &&
            'Item Ledger Entry'[Location Code] = MAX('FullInventoryDates'[Location Code]) &&
            'Item Ledger Entry'[Posting Date] <= CurrentDate
        )
    )
VAR InventoryValue = 
    CALCULATE(
        SUM('Item Ledger Entry'[Quantity]),
        FILTER(
            ALL('Item Ledger Entry'),
            'Item Ledger Entry'[Item No.] = MAX('FullInventoryDates'[Item No.]) &&
            'Item Ledger Entry'[Location Code] = MAX('FullInventoryDates'[Location Code]) &&
            'Item Ledger Entry'[Posting Date] <= LastEntryDate
        )
    )
RETURN IF(ISBLANK(InventoryValue), 0, InventoryValue)

注意：如果Navision的Quantity字段是变动量（入库为正、出库为负），则SUM到最后变动日期即可得到当前库存；如果是累计库存值，则将SUM改为LASTNONBLANK('Item Ledger Entry'[Quantity], 1)直接取最后一次的记录值。

4. 更简洁的替代方案（直接生成带库存的完整表） #

如果不需要单独的组合表，也可以用GENERATEALL直接生成包含库存的完整数据集：

Full Inventory = 
GENERATEALL(
    CROSSJOIN(
        CALENDAR(MIN('Item Ledger Entry'[Posting Date]), MAX('Item Ledger Entry'[Posting Date])),
        DISTINCT(SELECTCOLUMNS('Item Ledger Entry', "Item No.", [Item No.], "Location Code", [Location Code]))
    ),
    VAR CurrentDate = [Date]
    VAR LastInventory = 
        LASTNONBLANKVALUE(
            FILTER('Item Ledger Entry', [Posting Date] <= CurrentDate && [Item No.] = EARLIER([Item No.]) && [Location Code] = EARLIER([Location Code])),
            SUM('Item Ledger Entry'[Quantity])
        )
    RETURN ROW("Inventory", IF(ISBLANK(LastInventory), 0, LastInventory))
)

关于SQL交叉连接失效的可能原因 #

你之前尝试的SQL交叉连接没生效，大概率是以下问题：

• 没有生成全量的日期-物品-地点组合，比如日期范围不全或遗漏了部分物品/地点
• 关联库存值时没有正确筛选出每个组合的最近一次变动记录，导致无变动日期的库存值为空

DAX在处理这种上下文筛选和时间智能场景时，比SQL更适配Power BI的模型环境，尤其是可以直接利用模型中的关系和上下文计算。

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