PowerBI：如何基于共享X轴双Y轴按分组汇总数据

原始数据 #

ITEM    |RAISED_DATE    |STATUS |CLOSED_DATE
A       |2022-12-25     |CLOSED |2023-02-28  // 注：原数据中2023-02-31为无效日期，已修正为2023-02-28
B       |2023-01-26     |CLOSED |2023-02-02
C       |2023-01-28     |OPEN   |
D       |2023-02-01     |OPEN   |
E       |2023-02-14     |CLOSED |2023-02-06  // 注：提报日期晚于关闭日期，按原始数据保留
F       |2023-02-18     |CLOSED |2023-03-02
G       |2023-02-28     |OPEN   |
H       |2023-03-03     |CLOSED |2023-03-08

期望汇总结果 #

PERIOD  |RAISED_QTY |CLOSED_QTY
2022-12 |1          |0
2023-01 |2          |0
2023-02 |4          |3
2023-03 |1          |2

当前问题 #

已创建两个度量值统计提报和关闭总数量，总计数值正确，但按周期汇总时结果异常：

• 以RAISED_DATE（格式YYYY-MM）为X轴时，关闭数量统计错误：

  PERIOD  |RAISED_QTY |CLOSED_QTY
  2022-12 |1          |1
  2023-01 |2          |1
  2023-02 |4          |2
  2023-03 |1          |1
  

• 以CLOSED_DATE（格式YYYY-MM）为X轴时，提报数量统计错误且出现空周期：

  PERIOD  |RAISED_QTY |CLOSED_QTY
          |3          |0
  2023-02 |3          |3
  2023-03 |2          |2
  

尝试创建日历表并建立日期关联，但问题未解决，需找到正确的可视化实现方式。

解决方案指引 #

1. 构建独立无关联日历表 #

创建独立的日历表，不要与原始表的RAISED_DATE或CLOSED_DATE建立任何关系。日历表需包含Period（YYYY-MM格式）和Date（每日日期）列，覆盖数据中的所有日期范围。

用DAX创建日历表：

Calendar = 
ADDCOLUMNS (
    CALENDAR ( MIN('原始表'[RAISED_DATE]), MAX('原始表'[CLOSED_DATE]) ),
    "Period", FORMAT ( [Date], "YYYY-MM" )
)

2. 编写正确的度量值 #

基于日历表的Period维度，编写度量值时忽略原始表的日期筛选，手动匹配周期：

提报数量度量值

RAISED_QTY = 
VAR _periodCount = CALCULATE (
    COUNT('原始表'[ITEM]),
    REMOVEFILTERS('原始表'),
    FORMAT('原始表'[RAISED_DATE], "YYYY-MM") = SELECTEDVALUE('Calendar'[Period])
)
RETURN IF(ISBLANK(_periodCount), 0, _periodCount)

关闭数量度量值

CLOSED_QTY = 
VAR _periodCount = CALCULATE (
    COUNT('原始表'[ITEM]),
    REMOVEFILTERS('原始表'),
    '原始表'[STATUS] = "CLOSED",
    NOT(ISBLANK('原始表'[CLOSED_DATE])),
    FORMAT('原始表'[CLOSED_DATE], "YYYY-MM") = SELECTEDVALUE('Calendar'[Period])
)
RETURN IF(ISBLANK(_periodCount), 0, _periodCount)

3. 配置可视化组件 #

• 矩阵/折线图的X轴选择日历表的Period字段；
• Y轴添加上述两个度量值；
• 若需排序周期，可在日历表中添加YearMonthNumber列（如202212），以此为排序依据：

  "YearMonthNumber", YEAR([Date])*100 + MONTH([Date])
  

关键原理 #

直接使用原始表日期作为维度时，会自动筛选该日期关联的所有数据（如选RAISED_DATE时，关闭数量仅统计同一提报周期内关闭的项目）。通过独立日历表+无关联关系+度量值手动匹配周期，才能实现按周期分别统计提报和关闭的独立数据，避免维度筛选的干扰。

内容的提问来源于stack exchange，提问作者Héctor José Hernández Reinoza