Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).

MDX Utility Belt of Calculations Part 4

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 1 – ParallelPeriod

Part 2 – PrevMember

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])

            ) 

Comments

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.

Leave a Comment

Please register or log in to leave a comment.