Get the number of days per month - mdx
In TSQL, I can do this to get the number of days per 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 do I get the same functionality in MDX? Postscript I need the result to be an int.
+3
source to share
2 answers
If you have a hierarchy with the date of the year, it can be done like this:
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 pass the month value to the slicer. The computed term will do the rest.
SELECT NumOfMonths ON 0
FROM [YourCube]
WHERE ([Date].[Calendar Date].[Month].&[Dec-2015])
+3
source to share
This is the method we are using:
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:
0
source to share