Last Complete Month

Question

The following returns the last member in our set of months:

TAIL([Date].[Date - Calendar Month].[Calendar Month],1)

Our cube contains data upto and including yesterday - so if the above is run today it returns the member [July 2013].

I want the Last Completed Month so if run today (4th July) it should return [June 2013].

Not sure if this adds some extra complexity but if the expression is run on the first of a month then the last member in the hierarchy will actually be equal to the Last Complete Month.

So sometime the last completed month is the last member, and sometime it is the next but last member, in the hierarchy [Calendar Month]

Is there a fool-proof way of coding this expression?

Answer 1

You can determine easily if 'now' is the first day of month as following:

IIF( Day(Now()) = 1, ... , ... )

So you can create a calculated member :

with member [Last Completed Month] as
    IIF( Day(Now()) = 1,
         TAIL([Date].[Date - Calendar Month].[Calendar Month],1)(0),
         TAIL([Date].[Date - Calendar Month].[Calendar Month],2)(0)
    )

dunno about the [Calendar Month] hierarchy structure, but perhaps using a lastChild and lastChild.prevMember would be more efficient...

[edit] Tail( ... )(n) retrieve the n-th element of the set returned by Tail - this is a shortcut of item(n)

[edit] imagine the month hierarchy is flat under a all member: [Calendar Month].[All].lastChild would do the same as the Tail() and lastChild.prevMember to get the one before the last.