Subscribe to this blog
BriefcaseThis 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])
)
Posted by Steve Jones on 7 December 2009
Nice series, Devin. If I ever figure out how to work with MDX, I'm sure these will help.
Posted by knight_devin@hotmail.com on 7 December 2009
Yeah I think it's pretty tough to learn myself. The main reason I decided to do the series is for myself! I always forget how to write these kind of calculations so I did it for reference purposes. So you could call these selfish blogs.
Posted by manuelmaduka on 7 December 2009
Thanks all the same. Very short and sweet !
Posted by Boris on 7 December 2009
Thanks very much!
Posted by terrell.sharp on 14 December 2009
As they say in the commercials... PRICELESS. Thank you.