i would like to know how i could get the last value of a measure.

scenario: i have a time.yearly, time.monthly, time.weekly and time.daily;
measures.no_of_workers
for time.daily = sum(no_of_workers)
for time.weekly = lastvalue of no_of_workers in the week.. meaning if my
last day for the week is today 23jun2005, then i will get that value. the
other values for the week is irrelevant.. same goes for monthly.. and yearly..

This MSDN paper on semi-additive measures discusses how to compute
closing balances:

http://msdn.microsoft.com/library/de.../en-us/dnsql2k
/html/semiadd2.asp

Amir Netz
Microsoft Corporation

Updated May 18, 2004

Applies to:
Microsoft SQL Server 2000
Microsoft SQL Server 2000 Analysis Services

Summary: Focusing on a classic inventory problem, this article describes
the implementation techniques of semiadditive measures in online
analytical processing. (10 printed pages)
...
A common real-world problem can arise during implementation of the
closing balance expressions. In many implementations, the Time dimension
is defined with future time period members already contained in the
dimension. The ClosingPeriod([Month]) function does not analyze future
and past time periods. It only traverses the members' hierarchy tree to
find the last leaf descendant under the given member.

For example, take the example of implementing a closing balance
expression with a current date of mid-October 1998. Asking for the
closing balance for 1998 arguably should provide the data from the last
snapshot of the year, or the October snapshot. However, the
ClosingPeriod([Month]) function returns December 1998 as the closing
period of 1998. Because no snapshot exists for December 1998, the
closing balances return NULL.

This more sophisticated expression solves the problem:

Measures.[Last Non Empty Value]:
IIf(IsEmpty((Measures.[Value], Time.CurrentMember) ,
(Measures.[Last Non Empty
Value],Time.CurrentMember.PrevMember),Measures.[Value])
Measures.[Closing Value]: (Measures.[Last Non Empty Value],
ClosingPeriod([Month]))

In this example, the [Last Non Empty Value] measure has a recursive
expression that checks to see whether the value of the current cell is
empty. If it is empty, the expression moves back to the previous period
on the time dimension and checks the [Last Non Empty Value] of the
previous period. The function continues to go back in time until a
nonempty value is found. The [Closing Value] measure can then use the
[Last Non Empty Value]. This recursive behavior helps ensure that, for
the last year, the values of the last snapshot are returned. The less
common CoalesceEmpty() function performs this exercise more efficiently.
This function is equivalent to the expression demonstrated earlier:

Measures.[Last Non Empty Value]:
CoalesceEmpty((Measures.[Value], Time.CurrentMember ) ,
(Measures.[Last Non Empty Value],Time.CurrentMember.PrevMember))

Another useful way to get the right closing period is to provide the
relevant dates as properties of the leaf node of the time dimension
properties and deal with the future periods by using date comparison
functions and string functions, for example:

Measures.[Closing Value]:
IIf(CDate(ClosingPeriod().Properties("Closing Date"))>Now()),

(Measures.[Value],StrToTuple(Format(Now(),"\[yyyy\]\.\[\Qq\]\.\[mmmm\]")
)),
(Measures.[Value], ClosingPeriod([Month])))

This expression extensively uses Microsoft Visual Basic® for
Applications functions to manage dates and strings. (Remember that the
Visual Basic for Applications function library is registered
automatically for use in MDX expressions.) The function checks to see if
the value of the user-defined property "Closing Date" shows a time
period in the future (greater than Now()). If so, the function
constructs the member name of the last snapshot time period by using the
Visual Basic for Applications Format() function with the Visual Basic
for Applications Now() function. Then, the string is converted to a
tuple to return the [Closing Value].
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

I too can not get adobe flash to download to my computer , I am really getting frustrated as I have some pictures that I want to see and it says I need adobe flash for me to get them and nothing happens, help

From http://www.developmentnow.com/g/112_...of-measure.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/