aggregate the average of a calculated member over a certain time

Quick Info

i have a Microsoft SQL Server Analysis Project where i have to aggregate the average of a calculated member over the a time range of the past 20 days. And i don´t know how to solve this.

What i have

My calculated member looks like:

CREATE MEMBER CURRENTCUBE.[Measures].[MyTotalSample]
 AS [Measures].[ValueA]-[Measures].[ValueB]-[Measures].[ValueC],
VISIBLE = 1, ASSOCIATED_MEASURE_GROUP = 'MySamples';

This [MyTotalSample] Member is related to a [time] dimension, so i get the [MyTotalSample] for each [Time].[Day].

The result looks like the table below, where for example the value from "January 27 2017" the total of all [MyTotalSample] values for "January 27 2017".

|---------------------------------|
|[Time].[Day]    | MyTotalSample  |
|---------------------------------|
|January 27 2017 | 221            |
|January 28 2017 | 303            |
|January 29 2017 | 298            |
|January 30 2017 | 238            |
|---------------------------------|

That works so far.

PROBLEM

But what i want to do also is a per day average of [MyTotalSample] within the range from the current day to 20 days in the past.

To get something like the table below, where the value from "January 27 2017" the average value of [MyTotalSample] in a the range from "January 27 2017" to "January 08 2017".

|------------------------------------|
|[Time].[Day]    | MyTotalSample(Avg)|
|----------------|-------------------|
|January 27 2017 | 224               |
|January 28 2017 | 228               |
|January 29 2017 | 231               | 
|January 30 2017 | 226               | 
|------------------------------------|

What would be perfect is to ignore the null values... but that not so important right now...

I hope someone has experience with that and could give me a strategie how to do this...

Thanks you for taking time

Felix

Solution

We found a solutions in a msdn!

CREATE MEMBER CURRENTCUBE.[MEASURES].[MyTotalSample Moving Average]
 AS Avg
(
    {
            [Time].[Hierarchy].CurrentMember.Lag(20) 
        :
            [Time].[Hierarchy].CurrentMember
    }
    ,
    [Measures].[MyTotalSample]
), 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'MyDisplayFolder';