MDX / Analysis Services Calculating Month To Date

Question

I don't usually get involved with MDX if I dont have to, but....

I have a cube with simple fact table linked to a time dimension (with date,month,year columns and so on) and I want to be able for a given day of the month calculate the month to date total for that day.

In SQL it would be a doddle, however I'm not sure of how to achieve this in MDX.

Update I have an example working for a fixed date. Now I want to know how to do this for any possible date

WITH
MEMBER MTD_15_Feb_2010 AS
Aggregate
(
  MTD([Date].[Year Name -  Quarter Name -  Month Name -  Name].[Name].[02/15/2010]),
  [Measures].[Value]
)
SELECT
{
 MTD_15_Feb_2010
} ON 0
FROM [Cube]

If you can provide more details about your cube and time dimension, I can provide an example MDX in my answer. — Eric Weilnau

Eric Weilnau Eric Weilnau · Accepted Answer · 2010-10-18T14:50:11

You should be able to create the MDX using the MTD function. It will return the set of all days from the first of the month up to the member passed to the function.

Edit: There are multiple ways to update your example for any day. I would suggest replacing the date member [02/12/2010] with the CURRENTMEMBER function. This would also allow you to display the value for multiple dates as in the example below.

WITH MEMBER [MTD_Value] AS AGGREGATE (
      MTD([Date].[Year Name -  Quarter Name -  Month Name -  Name].[Name].CURRENTMEMBER)
      , [Measures].[Value]
    )
SELECT [MTD_Value] ON 0
  , {
      [Date].[Year Name -  Quarter Name -  Month Name -  Name].[Name].[02/15/2010]
      , [Date].[Year Name -  Quarter Name -  Month Name -  Name].[Name].[01/15/2010]
      , [Date].[Year Name -  Quarter Name -  Month Name -  Name].[Name].[02/15/2009]
    } ON 1
FROM [Cube]

MDX / Analysis Services Calculating Month To Date

1 Answers