Calculated measure to find the datediff using timedimension

0
votes

I need to find out the number of days in Month based on Time dimension. Time dimension is Month when Jan is selected it has to return 31 as value.

2
ssasmdxmdx-query
you must add your current query to the question, even if it is incomplete. This shows you have done some effort. – MoazRub
This is what I tried Datediff("d",[PostingMonth].[PostingMonth].CURRENTMEMBER.lag(3),[PostingMonth].[PostingMonth].CURRENTMEMBER.lag(0)) – SKenneth

2 Answers

0
votes

If you have Time dimension and Time hierarchy, this should work:

WITH MEMBER measures.NumOfDays AS

Count
(
Descendants
(
[Time].[Time].CurrentMember,
,LEAVES
)
)

SELECT Measures.NumOfDays ON 0,
[Time].[Time].Month on 1 
FROM [MyCube]
0
votes

The below sample shows how to get the count. Please note the below query only show the idea how to do this. Your cube will note have these attributes you you need to replace them

with member measures.t as Count(([Date].[Month of Year].&[1],[Date].[Day of Month].[Day of Month].members)) select {measures.t} on columns from [Adventure Works]