This is part four in the series of blog posts that will help in building a library of calculations you can use as a reference in any analysis services cube you build.
You can find the previous posts to this blog series below:
Part 3 – PrevMember minus CurrentMember
All of these blog posts are formatted to give you the business problem, a general solution and then the calculation needed to finish the job.
Problem
You need to show company sales year to date, quarter to date, and month to date
Solution
Use the PeriodsToDate function to return the sales YTD, QTD and MTD. There are also MDX functions called YTD, QTD, MTD and WTD but I have had more success using the PeriodsToDate function
Calculations
The sales amount returned YTD then aggregated
Aggregate(
PeriodsToDate(
[Date].[Date Hierarchy].[Year],
[Date].[Date Hierarchy].CurrentMember
),
([Measures].[Sales Amount])
)
—————————————————————
The sales amount returned QTD then aggregated
Aggregate(
PeriodsToDate(
[Date].[Date Hierarchy].[Quarter],
[Date].[Date Hierarchy].CurrentMember
),
([Measures].[Sales Amount])
)
—————————————————————
The sales amount returned MTD then aggregated
Aggregate(
PeriodsToDate(
[Date].[Date Hierarchy].[Month],
[Date].[Date Hierarchy].CurrentMember
),
([Measures].[Sales Amount])
)
Devin Knight 