Extend the SSAS time intelligent function of the “Add business intelligence” wizard

Last week i made a blog post about the power of the “Add business intelligence” wizard to add the current year to date. I decided to extend the script to add 2 new values with Previous YTD and current period last year.

All I did was copy and paste the automaticly created script and change the PeriodsToDate to get 12 months before [Date].[Dates].CurrentMember (last year) using Parallelperiod and give the measure a new name. This results in the following script:

Create Member
  CurrentCube.[Date].[Date Calculations].[Previous YTD]
  As "NA" ;
Scope(
       {
         [Measures].[Sales Amount]
       }
) ;
// Year to Date
  (
    [Date].[Date Berekening].[Previous YTD] ,
    [Date].[Calendar Year].[Calendar Year].Members,
	[Date].[Calendar Month].Members
  )
  =
  Aggregate(
             { [Date].[Date Berekening].[Current Periode] }
             *
            PeriodsToDate(
                            [Date].[Dates].[Calendar Year],
                             Parallelperiod(
                                            [Date].[Dates].[Calendar Month],
                                             12,
                                             [Date].[Dates].CurrentMember
                                            )
             )
  ) ;
End Scope ;

As second member i created a current period last year:

Create Member
  CurrentCube.[Date].[Date Calculations].[Current period last year]
  As "NA" ;
Scope(
       {
         [Measures].[Sales Amount]
       }
) ;
// Year to Date
  (
    [Date].[Date Berekening].[Current period last year] ,
    [Date].[Calendar Year].[Calendar Year].Members,
	[Date].[Calendar Month].Members
  )
  =
  Aggregate(
             { [Date].[Date Berekening].[Current Periode] }
             *
            Parallelperiod(
                               [Date].[Dates].[Calendar Month],
                               12,
                               [Date].[Dates].CurrentMember
                               )
  ) ;
End Scope ;

As you can see here we have removed the periodstodate and kept only Parallelperiod to get 12 months before [Date].[Dates].CurrentMember (last year).

This way you can add your own time intelligent functions very easy.