Hi,
I am stucked in a situation in MDX where I need to recognize if Current
day is the last Day of the Month. For example, I need to know if 28th
February is the last day in Feb or if this is the leap year so 29th
would be the last day. Similarly, I need to know for other months as
well like December always got 31 days. I am in a quest of readily
available functionality or the most efficient way for all type of
format such as January may be represented as (01,1,Jan,January). I
appreciate if you would help a naive writer of MDX
Regards
--danishnajam

You can use code like the following to figure out if a date is the last
day of the month. It uses functions from the VBA library to add a day to
the selected date and see if that gives you day 1 of the next month, if
so, you must have passed in the last day of the month. I am simply
returning 1 if the date is the last day of the month and 0 if it is not.

If by "current date" you are referring to the actual date you can
replace the CDate("31 Dec 2005") call with the Now() function. On the
other hand if you want to evaluate this for a dimension member you would
ideally pass in a date attribute (AS 2005) or member property (AS 2000)
or you could try parsing a member name.

This example is for AS 2005, but the same syntax works in AS 2000:

WITH
MEMBER measures.LastDayOfMonth as
'IIF(Day(DateAdd("d",1,CDate("31 Dec 2005")))=1,1,0)'
SELECT {measures.LastDayOfMonth} ON COLUMNS
FROM [Adventure works]


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

In article <1145627586.640511.53220 (AT) v46g2000cwv (DOT) googlegroups.com>,
danishnajam (AT) gmail (DOT) com says...