基于多日值的动态每日移动平均计算问题求助

问题说明 #

需要实现可动态调整的日维度移动平均：当参数设为N时，计算最近N天的每日平均值的均值。目前单日存在多个值时，多日参数下计算结果异常，已实现单值场景的移动平均，需适配日维度聚合的场景。

现有数据表结构及数据 #

数据表TBUData如下：

当前单日移动平均计算正常，但参数设为4天时结果异常。

当前使用的度量值代码 #

Moving Avg Per Day = 
VAR _LastDate =
MAX (TBUData[Date])
VAR _Duration = [Parameter By Day]
VAR _CalculationPeriod =
FILTER (
ALL ( TBUData),
AND (
TBUData[Date] > _LastDate - _Duration, 
TBUData[Date] <= _LastDate 
)
)
VAR _MovingAverage =
IF (
COUNTROWS ( _CalculationPeriod ) >= _Duration, 
CALCULATE (
AVERAGEX(TBUData,[M2Average]), 
_CalculationPeriod
)
)
RETURN
_MovingAverage

问题根源 #

1. COUNTROWS(_CalculationPeriod)统计的是数据表的行数而非唯一日期数，单日多行会导致误判有效天数
2. 直接使用AVERAGEX(TBUData,[M2Average])会重复计算单日的多行数据，导致结果偏离日维度的平均逻辑

修正后的度量值代码 #

Moving Avg Per Day = 
VAR _Duration = [Parameter By Day]
VAR _CurrentDate = MAX(TBUData[Date])
-- 获取当前日期及之前连续_Duration天的唯一日期集合
VAR _DateRange = 
    CALCULATETABLE(
        VALUES(TBUData[Date]),
        DATESINPERIOD(TBUData[Date], _CurrentDate, -_Duration, DAY)
    )
-- 统计有效日期数量
VAR _ValidDateCount = COUNTROWS(_DateRange)
-- 按日期聚合每日平均
VAR _DailyAverages = 
    CALCULATETABLE(
        SUMMARIZE(TBUData, TBUData[Date], "DailyAvg", [M2Average]),
        _DateRange
    )
-- 计算日维度移动平均（仅当有效日期数达标时返回）
VAR _MovingAverage = 
    IF(
        _ValidDateCount >= _Duration,
        AVERAGEX(_DailyAverages, [DailyAvg])
    )
RETURN _MovingAverage

关键优化点 #

• 使用VALUES(TBUData[Date])提取唯一日期，避免单日多行的干扰
• DATESINPERIOD函数更精准地获取连续N天的日期范围，适配非连续日期的数据场景
• 先通过SUMMARIZE按日期聚合每日平均，再计算这些每日平均的均值，严格遵循日维度的移动平均逻辑
• 用有效日期数而非行数判断是否满足计算条件，避免误判

主日期表适配建议 #

若已配置主日期表（Date Table），建议替换代码中的TBUData[Date]为主日期表的日期列，这样即使TBUData中存在无数据的日期，也能保证日期范围的连续性，计算结果更准确：

Moving Avg Per Day = 
VAR _Duration = [Parameter By Day]
VAR _CurrentDate = MAX(DateTable[Date])
VAR _DateRange = 
    CALCULATETABLE(
        VALUES(DateTable[Date]),
        DATESINPERIOD(DateTable[Date], _CurrentDate, -_Duration, DAY)
    )
VAR _ValidDateCount = COUNTROWS(_DateRange)
VAR _DailyAverages = 
    CALCULATETABLE(
        SUMMARIZE(TBUData, TBUData[Date], "DailyAvg", [M2Average]),
        TREATAS(_DateRange, TBUData[Date])
    )
VAR _MovingAverage = 
    IF(
        _ValidDateCount >= _Duration,
        AVERAGEX(_DailyAverages, [DailyAvg])
    )
RETURN _MovingAverage

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