# calculate measure using dates

### Question

• I am facing difficulty to calculate date difference of two dates

Dimension contains ID, Name, CreateDate, CloseDate

I would like to calculate CreateDate - CloseDate but i am getting #error by creating calculate measure within the CUBE

datediff("d",[Dim].[CreateDate].currentmember.membervalue,[Dim].[CloseDate].currentmember.membervalue)

but same statement is working using MDX

with Member [Measures].[Age] as

datediff("d",[Dim].[CreateDate].currentmember.membervalue,[Dim].[CloseDate].currentmember.membervalue)

select [Age] on 0,

{[Dim].[Id].children*[Dim].[CloseDate].children*[Dim].[CreateDate].children} on 1

from [cube]

Any idea, where am i doing mistake OR what is the way to calculate within the cube?

RaghuM

Thursday, July 17, 2014 8:27 PM

### All replies

• Hello ,

Try something as below.

with member agg as
cdate([Date].[Date].&[20050228].membervalue)-cdate([Date].[Date].&[20050201].membervalue)

select agg on 0 from [Adventure Works]

Regards,

Bharath

• Proposed as answer by Friday, July 18, 2014 9:26 AM
Friday, July 18, 2014 6:32 AM
• Hi Raghum,

According to your description, there are two columns CreteDate and CloseDate in your table, you want to add a calculation to calculate the value (CreateDate-CloseDate), right?

In this case, you can add a calculation to your data source view, and then use the calculated column as the new measures. A named calculation is a SQL expression represented as a calculated column. This expression appears and behaves as a column in the table. Please refer to the link below to see the details.
http://msdn.microsoft.com/en-us/library/ms174859.aspx

Besides, here is a similar issue with your, please see:
http://social.msdn.microsoft.com/forums/sqlserver/en-US/7cbf7471-141f-48ee-a6e3-2f3298dca2eb/date-difference-calculation-in-ssas

Regards,

Charlie Liao
TechNet Community Support

Friday, July 18, 2014 9:04 AM
• Thanks Liao, so far, i did calculated in DSV but I would like to know is there a way to calculate using calculations tab in CUBE itself.

RaghuM

Friday, July 18, 2014 3:01 PM