DAX需求：筛选下求实际超预算的最大日期（支持汇总层级）

示例数据表 #

需求说明 #

编写DAX度量值，在切片器筛选条件下，找出累计实际大于累计预算的最大日期，要求同时支持项目层级和主项目汇总层级的计算：

• 当切片器选中Mar-24时：
  • 项目A返回Feb-24（该日期累计预算600 < 累计实际900，为符合条件的最大日期）
  • 项目B返回Mar-24（累计实际1200 > 累计预算800）
  • 主项目X折叠时返回Mar-24（合计累计实际2100 > 合计累计预算1800）

期望矩阵展示结果 #

现有DAX的问题 #

以下DAX仅能在项目层级生效，无法支持主项目汇总层级的计算：

Max_date =
  var curDate = MAX(table[date]) // 获取当前上下文日期
  var curActual = SUM(table[Actual to Date])
  var result =
    CALCULATE(
      MAX(table[date]),
      table[Budget to Date] < curActual,
      table[date] < curDate,
      REMOVEFILTERS(table[date]) // 移除日期上下文
    )
  return result

优化后的DAX度量值 #

Max_Date_Over_Budget = 
VAR SelectedMaxDate = MAX(table[日期]) -- 若使用独立日期表，替换为MAX('Date'[Date])
VAR SummaryTable = 
    ADDCOLUMNS(
        SUMMARIZE(
            ALLSELECTED(table),
            table[日期],
            table[主项目],
            table[项目]
        ),
        "@累计预算", CALCULATE(SUM(table[累计预算])),
        "@累计实际", CALCULATE(SUM(table[累计实际]))
    )
VAR FilteredTable = 
    FILTER(
        SummaryTable,
        [@累计实际] > [@累计预算] && [日期] <= SelectedMaxDate
    )
VAR Result = 
    MAXX(
        GROUPBY(
            FilteredTable,
            table[主项目],
            table[项目],
            "@MaxDate", MAXX(CURRENTGROUP(), [日期])
        ),
        [@MaxDate]
    )
RETURN
    Result

优化要点 #

1. 统一层级计算逻辑：通过SUMMARIZE+ADDCOLUMNS生成主项目、项目、日期各维度的累计值，确保汇总层级和明细层级的计算规则一致。
2. 适配切片器筛选：ALLSELECTED保留切片器的日期范围限制，只在选中的日期内查找符合条件的记录。
3. 分组取最大日期：用GROUPBY按主项目和项目分组，提取每组内符合“实际超预算”条件的最大日期，解决汇总层级的计算问题。
4. 日期表适配：若模型中有独立日期表，将table[日期]替换为日期表的日期列，可进一步提升上下文一致性。

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