Power BI 12个月动态滚动总计计算问题求助

需求 #

计算每个月对应的过去12个月滚动订单总计，且新增月份数据时总计能自动更新。

测试数据（SQL） #

DECLARE @TBL TABLE(OrderDates date,Orders decimal(9,2))
INSERT INTO @TBL VALUES
('2021-01-01',160),('2021-02-01',412),('2021-03-01',101),('2021-04-01',35),('2021-05-01',497),('2021-06-01',254),
('2021-07-01',185),('2021-08-01',174),('2021-09-01',107),('2021-10-01',180),('2021-11-01',386),('2021-12-01',344),
('2022-01-01',222),('2022-02-01',18),('2022-03-01',46),('2022-04-01',313),('2022-05-01',136),('2022-06-01',457),
('2022-07-01',247),('2022-08-01',379),('2022-09-01',119),('2022-10-01',447),('2022-11-01',456),('2022-12-01',58),
('2023-01-01',346),('2023-02-01',229),('2023-01-01',18),('2023-02-01',210)
select * from @TBL

当前使用的DAX度量值 #

Running 12 months Total = 
var mm = 12
var rollingValue = CALCULATE(sum(Orders[Orders]),
  DATESINPERIOD(Orders[OrderDates] , LASTDATE(Orders[OrderDates]),-mm,MONTH))
return
rollingValue

问题根源 #

1. LASTDATE(Orders[OrderDates])返回的是整个订单表的最后日期，而非当前行上下文对应的月份日期，导致所有行的滚动计算范围固定指向最后一个月，无法按每个月单独计算。
2. 未使用专用的日期表（标记为日期表），时间智能函数的上下文计算存在偏差，尤其当事实表同一月份有多条数据时。

正确实现方案 #

步骤1：创建并标记日期表 #

日期表是DAX时间智能函数的核心依赖，先创建日期表并标记：

Date = 
VAR MinDate = MIN(Orders[OrderDates])
VAR MaxDate = MAX(Orders[OrderDates])
RETURN
ADDCOLUMNS (
    CALENDAR ( MinDate, EOMONTH(MaxDate, 12) ), -- 提前扩展12个月，支持后续新增数据自动适配
    "Year", YEAR([Date]),
    "Month Number", MONTH([Date]),
    "Month Name", FORMAT([Date], "mmmm"),
    "Year-Month", FORMAT([Date], "yyyy-MM")
)

• 在Power BI中选中该表，进入「建模」选项卡，点击「标记为日期表」→「标记为日期表」。
• 建立关系：将Date[Date]与Orders[OrderDates]关联。

步骤2：正确的滚动总计度量值 #

12个月滚动总计 = 
VAR CurrentMonthEnd = ENDOFMONTH('Date'[Date])
RETURN
CALCULATE (
    SUM(Orders[Orders]),
    DATESINPERIOD ( 'Date'[Date], CurrentMonthEnd, -12, MONTH )
)

无日期表替代方案 #

如果暂时无法创建日期表，可使用以下度量值：

12个月滚动总计（无日期表） = 
VAR CurrentMonth = EOMONTH(SELECTEDVALUE(Orders[OrderDates]), 0)
RETURN
CALCULATE (
    SUM(Orders[Orders]),
    FILTER (
        ALL(Orders[OrderDates]),
        EOMONTH(Orders[OrderDates], 0) >= DATE(YEAR(CurrentMonth)-1, MONTH(CurrentMonth), 1)
        && EOMONTH(Orders[OrderDates], 0) <= CurrentMonth
    )
)

方案说明 #

• 使用日期表的方案稳定性更强，后续新增月份数据时，只要日期表包含该月份（提前扩展的范围已覆盖），滚动总计会自动更新。
• ENDOFMONTH('Date'[Date])会精准获取当前上下文对应的月份最后一天，确保每个月份的滚动范围是该月份往前推12个月的完整区间。

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