Power BI中基于日期过滤两表并求和的技术实现问题

核心问题分析 #

之前使用MAX(CutOffDate[CutOffDate])会忽略产品上下文，取所有产品的最大截止日期（2022/3/1），导致所有产品都按这个日期过滤，无法实现每个产品独立的日期筛选逻辑。

解决方案：利用DAX保留产品上下文筛选 #

1. 计算符合条件的销售数量（度量值） #

使用RELATED函数在产品上下文内获取对应截止日期，精准过滤销售数据：

Sales_Quantity = 
CALCULATE(
    SUM(Exported[Quantity]),
    FILTER(
        Exported,
        Exported[Date] <= RELATED(CutOffDate[CutOffDate])
    )
)

2. 计算符合条件的预测数量（度量值） #

同理，过滤预测表中截止日期之后的数据：

Forecast_Quantity = 
CALCULATE(
    SUM(Forecast[Quantity]),
    FILTER(
        Forecast,
        Forecast[Date] > RELATED(CutOffDate[CutOffDate])
    )
)

3. 合并计算总数量 #

将两个度量值相加，即可得到按产品、日期汇总的总数量：

Total_Quantity = [Sales_Quantity] + [Forecast_Quantity]

验证结果（示例数据） #

• Product A：

  • 销售数据保留2022/1/1至2022/2/1，每日数量10，总计320
  • 预测数据保留2022/2/2至2022/4/30，每日数量20，总计1760
  • 按日期汇总：1-2月1日显示10，2月2日至4月30日显示20

• Product B：

  • 销售数据保留2022/1/1至2022/3/1，每日数量5，总计300
  • 预测数据保留2022/3/2至2022/4/30，每日数量15，总计900
  • 按日期汇总：1-3月1日显示5，3月2日至4月30日显示15

备选方案：生成物理合并表（计算表） #

如果需要生成物理表用于后续分析，可通过以下步骤创建：

过滤销售表 #

Filtered_Sales = 
VAR SalesWithCutOff = 
    ADDCOLUMNS(
        SUMMARIZE(Exported, Exported[Product], Exported[Date], Exported[Quantity]),
        "CutOffDate", RELATED(CutOffDate[CutOffDate])
    )
RETURN
    SELECTCOLUMNS(
        FILTER(SalesWithCutOff, [Date] <= [CutOffDate]),
        "Product", [Product],
        "Date", [Date],
        "Quantity", [Quantity]
    )

过滤预测表 #

Filtered_Forecast = 
VAR ForecastWithCutOff = 
    ADDCOLUMNS(
        SUMMARIZE(Forecast, Forecast[Product], Forecast[Date], Forecast[Quantity]),
        "CutOffDate", RELATED(CutOffDate[CutOffDate])
    )
RETURN
    SELECTCOLUMNS(
        FILTER(ForecastWithCutOff, [Date] > [CutOffDate]),
        "Product", [Product],
        "Date", [Date],
        "Quantity", [Quantity]
    )

合并两个过滤表 #

Combined_Table = UNION([Filtered_Sales], [Filtered_Forecast])

之后可在该表上创建按日期汇总的度量值：Total_Quantity_By_Date = SUM(Combined_Table[Quantity])

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