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

)

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s