## Calculating MTD, YTD and ITD

 Author Message f9c882q 61644 SSC Rookie Group: General Forum Members Points: 32 Visits: 170 I have developed a Cube for client and want to implement the time intelligence on Date, Month and Year hierarchy but can't get it to work. I used the built in Time intelligence to create the script below unfortunately the same dollar values are repeated for ITD, MTD and YTD. Any help will be appreciated. Thanks in advanceCREATE MEMBER CURRENTCUBE.[ClaimsTransactionDate].[Calender ClaimsTransactionDate Calculations].[ITD] AS "NA", FORMAT_STRING = "Currency", VISIBLE = 1 ; CREATE MEMBER CURRENTCUBE.[ClaimsTransactionDate].[Calender ClaimsTransactionDate Calculations].[YTD] AS "NA", FORMAT_STRING = "Currency", VISIBLE = 1 ; CREATE MEMBER CURRENTCUBE.[ClaimsTransactionDate].[Calender ClaimsTransactionDate Calculations].[MTD] AS "NA", FORMAT_STRING = "Currency", VISIBLE = 1 ; Scope( { [Measures].[LossPaid], [Measures].[LossReserve], [Measures].[LossIncurred], [Measures].[LAEPaid], [Measures].[LAEReserve], [Measures].[LAEIncurred] }); // ITD ( [ClaimsTransactionDate].[Calender ClaimsTransactionDate Calculations].[ITD], [ClaimsTransactionDate].[Year].[Year].Members, [ClaimsTransactionDate].[Transaction Date].Members ) = Aggregate( { [ClaimsTransactionDate].[Calender ClaimsTransactionDate Calculations].[Current ClaimsTransactionDate] } * PeriodsToDate( [ClaimsTransactionDate].[Calender].[Year], [ClaimsTransactionDate].[Calender].CurrentMember ) ); // Year to Date ( [ClaimsTransactionDate].[Calender ClaimsTransactionDate Calculations].[YTD], [ClaimsTransactionDate].[Year].[Year].Members, [ClaimsTransactionDate].[Transaction Date].Members ) = Aggregate( { [ClaimsTransactionDate].[Calender ClaimsTransactionDate Calculations].[Current ClaimsTransactionDate] } * PeriodsToDate( [ClaimsTransactionDate].[Calender].[Year], [ClaimsTransactionDate].[Calender].CurrentMember ) ); // Month to Date ( [ClaimsTransactionDate].[Calender ClaimsTransactionDate Calculations].[MTD], [ClaimsTransactionDate].[Month].[Month].Members, [ClaimsTransactionDate].[Transaction Date].Members ) = Aggregate( { [ClaimsTransactionDate].[Calender ClaimsTransactionDate Calculations].[Current ClaimsTransactionDate] } * PeriodsToDate( [ClaimsTransactionDate].[Calender].[Month], [ClaimsTransactionDate].[Calender].CurrentMember ) ); End Scope; PB_BI SSCommitted Group: General Forum Members Points: 1944 Visits: 2515 Take a look at Marco Russo's brilliant DateTool dimension. It does a similar thing to the Time Intelligence stuff but it's much better and more configurable to whatever your needs are: http://sqlblog.com/blogs/marco_russo/archive/2007/09/02/datetool-dimension-an-alternative-time-intelligence-implementation.aspx I'm on LinkedIn