Exponential moving average in MDX

Question

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.

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

This is a useful solution for me, But it's not ignore any Null values.

Answer 1

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]