Search code examples
sql-servert-sqlmdxcube

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.

Solution

  • 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])