MDX中的指数移动平均线

Question

我有立方体的以下输出

date          value
----          -----------
2021-01-06    10
2021-01-07    Null
2021-01-08    Null
2021-01-09    15
2021-01-10    18
2021-01-11    25

我的问题是，如何在没有任何Null值的情况下用这些数据创建指数移动平均值。

https://social.msdn.microsoft.com/Forums/en-US/39f7074b-ca2d-435b-8612-1c71955b76a8/mdx-for-exponential-moving-average?forum=sqlanalysisservices

这对我来说是一个有用的解决方案，但它不能忽略任何Null值。

Answer 1

根据公式，您可以尝试以下计算度量：

[A1]: Case WHEN [MeasureName] = Null THEN 0 ELSE 1 END
[A2]: CASE WHEN A1 = 0 THEN Null ELSE SUM(Null:[DimDateTime].[Year_Month_Day].CurrentMember,[A1]) END
[A3]: 0.5 --For Example For (1-Alpha)
[A4]: [MeasureName] * ([A3]^[A2]) --For Numerator of a Fraction
[A5]: CASE WHEN [MeasureName] = Null THEN Null ELSE (A3^A2) END --For Denominator of a Fraction
[A6]: CASE WHEN ISEMPTY([A4]) THEN NULL ELSE
SUM 
(
TAIL
(
FILTER
(
{
[DimDateTime].[Year_Month_Day].CurrentMember.lag(100):[DimDateTime].[Year_Month_Day].CurrentMember --You Can Change Value 100 Depend on Your Data
} , not isempty ([A4])
)
,3  -- You Can Change This Value Depend on Your Business
),
[A4]
)
END
[A7]: CASE WHEN ISEMPTY([A5]) THEN NULL ELSE
SUM 
(
TAIL
(
FILTER
(
{
[DimDateTime].[Year_Month_Day].CurrentMember.lag(100):[DimDateTime].[Year_Month_Day].CurrentMember --You Can Change Value 100 Depend on Your Data
} , not isempty ([A5])
)
,3  -- You Can Change This Value Depend on Your Business
),
[A5]
)
END
[A8]: [A6]/[A7]