Hi All;

i would like to caclulate the age of employees , how can i do that in jet enterprise based on the date of birth.

Regards;

• Jet Reports Historic Posts

Problem solved with th folowing way :

1 - add a field in the Employee table called Age ( Age = DATTEDIFF(YAER, BIRTH_DATE, GETDATE() ) ).
2 - add two standar mesure : sum mesure of age , Employee count.
3 - Add a calculated mesure Average Age : Sum Mesure/ Employee Count.
4 - Hide the Sum of Age cuz i dont need it.

Result were perfect for me.

Regards ;

• Lenka Kureckova Eriksen

Hi,

I have a question - how would you do this, if you do not just want to know "as of today" (getdate), but should be able to chose a date or see development accross time.

Thank you!

• JaeW_atOnyx

It's tough b/c In the earlier solution you were building the date into the fact table which of course creates a static number.

Age = DATTEDIFF(YAER, BIRTH_DATE, GETDATE() )

If you want to have a dynamic date you have to use a variation of .CurrentMember and figure out how to extract the Member Value (instead of Name or Key) in a calculated measure

https://docs.microsoft.com/en-us/sql/mdx/currentmember-mdx

SSAS MDM is not super intuitive when it comes to date calculations, so it may be easiest to

1. have birthYear represented on your Customer table as an integer.
2. Add BirthYear to the Customer dimension.
3. Create a Calculated Measure of [Posting Date].[Year].CurrentMember.member_value - [Customer].[Birth Year].CurrentMember.member_value

For greater precision depending on what month of the year we're looking at, instead of doing calculations based on a calendar year, you may have to consider how Excel or R handle dates (as Integers instead of true DateValue

• where birthdate is represented as the number of days since some origin date eg. 1/1/1970
• consider how R handles it:  https://stats.idre.ucla.edu/r/faq/how-does-r-handle-date-values/
• In my example 1/1/1970 = 1, if you were born 12/31/1969 you have -1, and if you were born 1/2/1970 you have 2.
• you can still do the mathematical difference (which will then be in units of days and then you have to modulo divide to show whole years.

Hope that pseudocode gets you going.

Jae@onyxReporting.com

• Lenka Kureckova Eriksen

Thank you very much Jae!

Best regards,

Lenka