My collection of how to use MDX in a SSAS 2012 / 2014 environment.
Tuesday, March 13, 2018
Past 12 months dynamic
Wednesday, September 2, 2015
multiple first day of week
Friday, August 29, 2014
I was asked to make a simple ABC classification for a sales measures. I combined exists with TopPercent to identify:
- Class A = between the top 0 and 20 %
- Class B = between the top 20 and 80%
- Class C = between the top 80 and 100%
Friday, August 22, 2014
Calculating an Age
One of the questions you often get is how to calculate a customers age on a certain date. Luckily Chris Webb published a great example: http://cwebbbi.wordpress.com/2014/06/09/calculating-ages-in-mdx/.
I made a small extension to this by making it level aware, this means you can also use it at year, semester, quarter and month level.
To make it level aware I use the .Level.Ordinal extension on the Currentmember. This returns the Level of the member within the Hierarchy (0 = All, 1 = Year, 2 = Semester, 3 = Quarter, 4 = Month, 5 = date).
Sunday, August 17, 2014
CASE WHEN and NULL / 0 (zero)
When you have a CASE WHEN in MDX you basically have two options:
The Simple or Searched statement:
CASE [<<Set To Be Evaluated>>]
WHEN [<<condition1>>] THEN [<<action>>]
WHEN [<<condition2>>] THEN [<<action>>]
ELSE [<<action>>] END
Sunday, August 10, 2014
Calculating Median and Percentile
MEDIAN or 50th Percentile
According to Wikipedia the median or 50th percentile is the numerical value separating the higher half of a data sample, a population, or a probability distribution, from the lower half. Some examples:
|Set||Median / 50th percentile|
In this example you see that if the set contains an even number of members the median is the average between the numbers left and right from the centre.
Wednesday, August 6, 2014
YearToDate (YTD) Previous Year (also QTD, MTD, WTD)
This is simple is you combine ParallelPeriod with the YTD functionality:
MEMBER [Measures].[Sales Amount YearToDate]
SUM(YTD( [Date].[Calendar].CurrentMember),[Measures].[Sales Amount])
MEMBER [Measures].[Sales Amount Previous Year]
SUM(ParallelPeriod( [Date].[Calendar].[Calendar Year], 1,[Date].[Calendar].CurrentMember),[Measures].[Sales Amount])
MEMBER [Measures].[Sales Amount YearToDate Previous Year]
SUM(YTD( ParallelPeriod( [Date].[Calendar].[Calendar Year], 1,[Date].[Calendar].CurrentMember)),[Measures].[Sales Amount])