MDX Utility Belt of Calculations Part 4

Devin-Knight, 2009-12-07

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

            ) 

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads