I have bellow output of cube
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
My question is, how can I create exponential moving average with this data without any Null values.
This is a useful solution for me, But it's not ignore any Null values.
According to The Formula, You Can Try Below Calculated Measures:
[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]