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.