Hi all, i'm trying to get the last register value of a measure, i'm
doing this with:

([Measures].[Sales], ClosingPeriod([Sales Date].[day], [Sales
Date].CurrentMember))

This works perfect for me, but when i try to select multiple dates,
like two months Ex: April and June. I need the sale for the last day of
June, but it's return me a blank cells.

Any idea?

Depends on whether you're using AS 2000 or AS 2005, and what kind of MDX
queries the client app generates. With As 2005 and Excle 2003, this kind
of calculation could work:

([Measures].[Order Quantity],
Tail(Existing [Date].[Date].[Date]).Item(0).Item(0))

select {[Measures].[Order Quantity],
[Measures].[ClosingOrders]} on 0
from [Adventure Works]
where {[Date].[Calendar].[Month].&[2004]&[4],
[Date].[Calendar].[Month].&[2004]&[6]}
----------------------------------------------------
Order Quantity ClosingOrders
28,027 140

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thanks for your answer, I'm using AS 2005 and Excel 2003, in AS my fact
table had a lot of sales entries for any date this table is updated
every day so the sale for the last entry is the value i need to use in
a MDX calculated member.
I can use "closingperiod" to get the last sale value, but when i use
the multiple selection of date in Excel i don't get any data, the data
apeears in blank and I need to get the last sale value selected in the
multiple selection, do you have any idea to do this?
Becouse with your solution i get a static value and i need to do this
variable.

Thanks.

Could you explain this with an example scenario:
"Becouse with your solution i get a static value and i need to do this
variable"?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Ok, I have the data like this in the fact table:
Example 1.
Date Sales
06/04/13 100
06/04/14 120
06/04/14 140

When I process the cube every day the actual date is added with the
sale for this day, like this
Example 2.
Date Sales
06/04/13 100
06/04/14 120
06/04/14 140
06/04/15 150

I'm trying to do a calculated member and in the MDX statement i need to
get the last sale value every day, in example 1 i need to get the value
140, but at the next day i need to do the calculated member with the
150 value. You see I have varius values for sale every day but i only
need the last one.
Any idea?

Thanks.






Deepak Puri ha escrito:

Well, I think that I what I suggested would work. What results do you
get if you use something like:

([Measures].[Sales],
Tail(Existing [Sales Date].[Day].[Day]).Item(0).Item(0))


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thanks for your answer,
That works for me, but I'm testing this calculated member in AS2005
Standar and in AS2000
with AS2000 works perfect, but when I try in AS2005 it's giving to me a
#VALUE! error when I see it with Excel 2003, I really need to get this
working in AS2005.
I already try a lot but I don't find any answer why this work in 2000
and doesn't work with 2005?