## Wednesday, September 2, 2015

### multiple first day of week

Nice trick:

http://richardlees.blogspot.nl/2015/08/supporting-multiple-first-day-of-week.html

## Friday, August 29, 2014

### ABC Classification

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 1,2,3,4,5 3 1,2,3,3,4,5 3 1,2,3,4,4,5 3,5 1,2,3,3,4,4,5 3 1,2,3,3,4,4,5,5 3,5

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:

WITH
MEMBER [Measures].[Sales Amount YearToDate]
AS
SUM(YTD( [Date].[Calendar].CurrentMember),[Measures].[Sales Amount])

MEMBER [Measures].[Sales Amount Previous Year]
AS
SUM(ParallelPeriod( [Date].[Calendar].[Calendar Year], 1,[Date].[Calendar].CurrentMember),[Measures].[Sales Amount])

MEMBER [Measures].[Sales Amount YearToDate Previous Year]
AS
SUM(YTD( ParallelPeriod( [Date].[Calendar].[Calendar Year], 1,[Date].[Calendar].CurrentMember)),[Measures].[Sales Amount])