CODEWITHSUNDEEP
sql-servert-sqlmdxcube
Paul Kar.
Paul Kar.

Reputation: 1324

Get the amount of days in a month - mdx

In TSQL I can do this to get the amount of days in some month:

declare @date as datetime
set @date = '2015-02-06'
select datediff(day, dateadd(day, 1-day(@date), @date),
              dateadd(month, 1, dateadd(day, 1-day(@date), @date)))

How can I get the same functionality in MDX? P.S. I need the result to be an int.

Upvotes: 3

Views: 575

Answers (2)

whytheq
whytheq

Reputation: 35557

This is the method we use:

WITH 
  MEMBER [MEASURES].[NumOfDaysInMonth] AS 
    IIF
    (
      VBA!Isdate([Date].[Calendar].CurrentMember.Name)
     ,Datepart
      ("D"
       ,
          Dateadd
          ("M"
           ,1
           ,Cdate
            (
                Cstr(VBA!Month([Date].[Calendar].CurrentMember.Name)) + "-01-"
              + 
                Cstr(VBA!Year([Date].[Calendar].CurrentMember.Name))
            )
          )
        - 1
      )
     ,''
    ) 
SELECT 
  NON EMPTY 
    {[MEASURES].[NumOfDaysInMonth]} ON 0
 ,NON EMPTY 
    {
      [Date].[Calendar].[All]
     ,[Date].[Calendar].[Calendar Year].&[2005]
     ,[Date].[Calendar].[Calendar Semester].&[2008]&[2]
     ,[Date].[Calendar].[Month].&[2006]&[3]
     ,[Date].[Calendar].[Date].&[20060214]
     ,[Date].[Calendar].[Month].&[2007]&[11]
    } ON 1
FROM [Adventure Works];

The above returns the following:

enter image description here

Upvotes: 0

SouravA
SouravA

Reputation: 5243

If you have a year-month-date hierarchy in place, it could be done in the below fashion:

WITH MEMBER NumOfDaysInMonth AS
DateDiff(       
            "d",
             HEAD(DESCENDANTS([Date].[Calendar Date].CURRENTMEMBER, 1)).ITEM(0).MEMBER_CAPTION, //Gets the first date of the month
             TAIL(DESCENDANTS([Date].[Calendar Date].CURRENTMEMBER, 1)).ITEM(0).MEMBER_CAPTION  //Gets the last date of the month
        ) + 1

You just need to to pass the month's value in the slicer. The calculated member will do the rest.

SELECT NumOfMonths ON 0
FROM [YourCube]
WHERE ([Date].[Calendar Date].[Month].&[Dec-2015])

Upvotes: 3

Related Questions