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:

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


-- 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
  MEMBER measures.dateforcalculation AS
  WHEN [Date].[Calendar].currentmember.level.ordinal = 1 THEN
  WHEN [Date].[Calendar].currentmember.level.ordinal = 2 THEN
  WHEN [Date].[Calendar].currentmember.level.ordinal = 3 THEN
  WHEN [Date].[Calendar].currentmember.level.ordinal = 4 THEN
  ELSE [Date].[Calendar].currentmember.member_value
  --calculate the difference between the years of the 
  --current date and the customer's birth date
  MEMBER measures.yeardiff AS
      [Customer].[Customer].currentmember.PROPERTIES("Birth Date", typed),
  --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(
  --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",
  --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
      WHEN [Date].[Calendar].currentmember.level.ordinal = 0 THEN null
      ELSE measures.yeardiff -
    IIF(measures.datemonthday>=measures.birthmonthday, 0, 1)
  } ON 0,
  --return all customers in Coff's Harbour on rows
        [Customer].[Customer Geography].[City].&[Coffs Harbour]&[NSW],
        [Customer].[Customer Geography].[Customer]
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

1 comment:

  1. Hi,

    find Age Calculator then it is Overall looking for a Chronological age or anniversary calculator then this is your best option in age Calculator.

    Age Calculator By Date Of Birth (Days, Months)
    Calculation of age with interesting details!
    If you want to know the details of your age, then install the Age Calculator