Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculating Age on a Given Date

Hi,   I have a table with client details, including date of birth, how would I go about calculating the age of these clients on any given date (Which the end user can select). The aim of the exerci...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Anonymous

     

    My apologies for late reply

     

    You can first create a small parameter table defining your age buckets for example

     

    Bucket Age End Age Start
    Infant 2 0
    Teenager 20 2
    Adult 60 20
    Senior Citizen 200 60

     

    Then you can use a MEASURE like this

     

     

    Measure =
VAR AgeStart =
    SELECTEDVALUE ( Buckets[Age Start] )
VAR AgeEnd =
    SELECTEDVALUE ( Buckets[Age End] )
RETURN
    COUNTROWS (
        FILTER (
            VALUES ( Table1[Client Name] ),
            [BirthDate1] > AgeStart
                && [BirthDate1] <= AgeEnd
        )
    )

     

    [BirthDate1] is the Measure we created before

    Please see the attached file for clarity




Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

Anonymous

 

Hi Mohit

 

You can create a Calendar Table for selecting dates

Then you can use one of these MEASURES to get the age at a specified date

 

Please see the attached file as well

 

BirthDate1 =
YEARFRAC ( SELECTEDVALUE ( Table1[BirthDate] ), MAX ( 'Calendar'[Date] ) )

BirthDate2 = INT(YEARFRAC(SELECTEDVALUE(Table1[BirthDate]),MAX('Calendar'[Date])))


Anonymous's avatar
Anonymous
Not applicable
8 years ago

Thanks Zubair, that works perfectly. However my conundrum is that I am unable to use this measure to graph out frequency of each age?

 

Also how would I go about grouping these ages in to buckets of 5 or distinct age groups like infant, teenager, adult, senior citizen, etc.

 

Thanks so much for the help!