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

 

WITH
-- Decide which date we are going to use
-- In this example we use the First date of a period
-- If you want to use the last date of a period
-- Change FirstChild To LastChild
-- Based on a orignal design from Chris Webb
-- http://cwebbbi.wordpress.com/2014/06/09/calculating-ages-in-mdx/
--
  MEMBER measures.dateforcalculation AS
CASE
  WHEN [Date].[Calendar].currentmember.level.ordinal = 1 THEN
[Date].[Calendar].currentmember.firstchild.firstchild.firstchild.member_value
  WHEN [Date].[Calendar].currentmember.level.ordinal = 2 THEN
[Date].[Calendar].currentmember.firstchild.firstchild.member_value
  WHEN [Date].[Calendar].currentmember.level.ordinal = 3 THEN
[Date].[Calendar].currentmember.firstchild.member_value
  WHEN [Date].[Calendar].currentmember.level.ordinal = 4 THEN
[Date].[Calendar].currentmember.member_value
  ELSE [Date].[Calendar].currentmember.member_value
END
  --calculate the difference between the years of the 
  --current date and the customer's birth date
  MEMBER measures.yeardiff AS
    datediff(
      "yyyy",
      [Customer].[Customer].currentmember.PROPERTIES("Birth Date", typed),
      measures.dateforcalculation
    )
  --calculate a value which is the month number
  --multiplied by 100 plus the day number of the month
  --for the current date
  MEMBER measures.datemonthday AS
    (month( measures.dateforcalculation) * 100) + day(
    measures.dateforcalculation)
  --calculate a value which is the month number
  --multiplied by 100 plus the day number of the month
  --for the customer birth date
  MEMBER measures.birthmonthday AS
    (month([Customer].[Customer].currentmember.PROPERTIES("Birth Date", typed))
    *
    100) + day([Customer].[Customer].currentmember.PROPERTIES("Birth Date",
    typed)
    )
  --calculate customer age as the difference in years
  --minus 1 if the customer's birthday this year is
  --after the current date
  --For all levels except (All)
  MEMBER measures.customerage AS
    CASE
      WHEN [Date].[Calendar].currentmember.level.ordinal = 0 THEN null
      ELSE measures.yeardiff -
    IIF(measures.datemonthday>=measures.birthmonthday, 0, 1)
    END
SELECT
  {
    [Date].[Calendar].allmembers
  } ON 0,
  --return all customers in Coff's Harbour on rows
  {
    (
      DESCENDANTS(
        [Customer].[Customer Geography].[City].&[Coffs Harbour]&[NSW],
        [Customer].[Customer Geography].[Customer]
      ),
      (
        measures.customerage
      )
    )
  } ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]  

This will get you:

The value for All Periods is correct due to the WHERE statement. With out the WHERE statement you get:

Have Fun And Till Next Time