MDX Puzzles: Super 8–Solution

This week’s puzzle was pretty straight forward, but it required you to use the OpeningPeriod function. To show the count of customers on the first day of the year, we’ll create a calculated measure called “First Day Customer Count”.

WITH MEMBER [Measures].[First Day Customer Count] AS 
(OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER),[Measures].[Customer Count])

In the above calculated measure, the first argument of the OpeningPeriod function specifies at which level we want the first period for and in this case we want the first period at the Date level. The second argument allows us to specify for which member we’d like to see the first day of the year.

Secondly, we’ll create a second calculated measure called “First Day”, which is very similar to our first calculated measure except we’ll be using the .NAME property to get the name of the first period of the specified level.

MEMBER [Measures].[First Day] AS
OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER).NAME

Then finally we’ll reference these to calculated measures in our final select statement.

WITH 
MEMBER [Measures].[First Day Customer Count] AS 
(OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER),[Measures].[Customer Count])

MEMBER [Measures].[First Day] AS 
OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER).NAME

SELECT {[Measures].[First Day],[Measures].[First Day Customer Count]} ON 0,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1

FROM [Adventure Works]

I’d like to say thanks to everyone who participated in this MDX puzzle. We had several great submissions but I can only pick one winner. Super 8’s puzzle winner is Jason Thomas (@de_unparagoned)! Congrats, Jason! I’ll be sending you one cool Kick’n SSAS t shirt in the mail as soon as I get off the road and back home next week.

Stay tuned for the next round of MDX Puzzles!

Advertisements

One thought on “MDX Puzzles: Super 8–Solution”

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