Tuesday, March 13, 2018

Past 12 months dynamic

{StrToMember("[Kalender].[J4MND].[Jaar 4MND Maand].&["+Format(DATEADD("M",-1,now()),"yyyyMM")+ "]").Lag(11):StrToMember("[Kalender].[J4MND].[Jaar 4MND Maand].&["+Format(DATEADD("M",-1,now()),"yyyyMM")+ "]")}

Wednesday, September 2, 2015

multiple first day of week

Nice trick:


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:

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