Calculating Age on a Given Date

[Anonymous]

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 exercise is to see the age demographics on any given date in the past.

Thanks

[Anonymous]

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 filter on). the aim of the exercise is to see the age demographics on any given date in the past.

Thanks

[Zubair_Muhammad · Accepted solution]

@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

[Anonymous]

Thanks @Zubair_Muhammad, don't need to apologies, I'm slower in responding.

Your solution worked, thanks!

[Zubair_Muhammad]

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

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!

[Sean]

https://www.sqlbi.com/blog/marco/2018/06/24/correct-calculate-of-age-in-dax-from-birthday/