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!
Yippeee :)…