Hello

We need an mdx that collects the last reported value for a measure in the
time dimension. Example:

Dates reported Measure

1 1000
2 no value
3 1400
4 no value
5 no value
6 no value
7 no value
8 1500

The expected result in the MDX shall be

1 1000
2 1000
3 1400
4 1400
5 1400
6 1400
7 1400
8 1500

Thx in advance

Best regards

Martin Kihl

You may create calculated member like this

iif(IsEmpty([Measures].[YouMeasure]),[Dates].PrevMember,[Measures].[YouMeasure])

This formula use recursion to obtain latest non empty value

Vladislav Beliaev

You may create calculated member like this:

iif(IsEmpty([Measures].[YouMeasure]),[Dates].PrevMember,[Measures].[YouMeasure])

Note, this formula use recursion to obtain latest non empty value

Vladislav Beliaev

Hi and thanks for your response

I don't get there really with this MDX - maybe I'm missing something. This
is only giving me the previous member in the time dimension if my value is
null. If the previous member also is null I will get null in this field as
well. I would like it to find the latest value with a number that occured
before the date I'm on.

This thing you write about recursion how do I perform that?

Best regards
Martin Kihl



"VBeliaev" wrote:

well if you do full processing.. here you go

make your dates in descending order and then you can just refer to
DATE(0).CHILDREN

The previous example should work. I will try and expand on the previous
answer for you.

Lets say that you have one measure in your cube called "ActualValue". We
will create a new calculated measure called "LastValue", that will show
the last value of "ActualValue". The formula for "LastValue" will look
like the following.

iif(IsEmpty([Measures].[ActualValue]),[Dates].PrevMember,[Measures].
[ActualValue])

It may actually help you see what is happening if I expand this formula
like so:

iif(IsEmpty([Measures].[ActualValue]),([Dates].PrevMember,Measures.
[LastValue]),[Measures].[ActualValue])

Lets start with the false case as that is fairly obvious. If ActualValue
is not empty, the we return that value.

Now comes the recursion bit, if ActualValue is empty we look in the
previous date member for the value of "LastValue". Because "LastValue"
is a formula it has to evaluate the expression again in order to return
the a value.

You can spot recursive formulas because they have a reference to their
own value in them somewhere. This was not immediately obvious in the
first example as the current member of the measures dimension is
implied.

In the above examples:

"[Date].PrevMember" - is the same as
"([Date].PrevMember,[Measures].CurrentMember)" - which is the same as
"([Date].PrevMemver,[Measures].[LastValue])"

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <D9BF3611-0AE4-447F-B808-41212DD60ABE (AT) microsoft (DOT) com>,
Martin (AT) discussions (DOT) microsoft.com says...