returning the last (not empty) month with MDX

• solaris

  SSC Veteran

  Points: 247

  More actions

  November 21, 2003 at 7:13 am

  #107106

  Hi everyone,

  I'm trying to create a calculated member that, for a given year, returns the last month that is not empty.

  This means that if december is empty, the calculated member will return the november value. I dont think a "ClosingPeriod" function will work (i tried it and it will always return zero, due to december having no values).

  Is there a way to filter only non empty months? If so, can anyone give me an example? Also, the probable function to use would be LastChild (i think!). If anyone has an example on this, please post it. The info for this on BOL is a little scarce.

  Thanks for your replies.

  Also,

• shanelillis

  SSC Enthusiast

  Points: 130

  More actions

  February 17, 2005 at 10:30 am

  #542877

  Hi,

  I came accross this post as I too was looking for a solution to the same problem. Seems that you created this post some time back and have probably found a solution by now

  Anyway, my solution might be of use to other users:

  We need to create 2 calculated measures:

  1) Closing Qty

  2) True Closing Qty

  Closing Qty uses a number of MDX functions (iif, IsEmpty, ClosingPeriod, Tail, Filter and Item). We are using a Time Dimension whose lowest level is Month and our Measure is Quantity. Closing Qty has the following MDX statement:

  iif

  (IsEmpty((ClosingPeriod(Month, Time.CurrentMember), Measures.Quantity))

  ,

  (Tail(Filter (Time.Month.Members,

  NOT

  IsEmpty ((Measures.Quantity, Time.CurrentMember))),1).Item(0))

  ,

  (ClosingPeriod(Month, Time.CurrentMember), Measures.Quantity))

  It looks a bit threatening but basically the statement checks to see if the Closing Period is null. If it is not null it uses the Closing Period function to display the closing qty. If it is null we identify the last month that is not null (using a combination of Tail, Filter, Item Functions) and use it as our closing period.

  This works fine, however, all future periods will also display the last current balance. So in your example, Oct, Nov and Dec will all have the same closing qty even though we are phyiscally in Oct and Nov and Dec are in the future.

  This might be ok for some situations but we would really like Nov and Dec to remain blank. This is where the second calculated measure "True Closing Qty" comes in. Its statement is more straightforward:

  iif (IsEmpty(([Measures].[Quantity],Time.CurrentMember))

  ,null

  ,[Measures].[Closing Qty])

  All we're saying here is that if the Quantity for the current member is null then display nothing. If its not null then display the Closing Qty, ie our first calculation that we created.

  We can set Visible property to False in the Advanced tab in the Properties of the Closing Qty Calculated Measure. This ensures that End Users will only see one calculation for the Closing Qty.

  Hope this helps!

  Shane