cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mohitg11 Frequent Visitor
Frequent Visitor

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

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculate age as of a specified date

@mohitg11

 

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

aged.png


6 REPLIES 6
mohitg11 Frequent Visitor
Frequent Visitor

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

 

Thanks

Super User
Super User

Re: Calculating Age on a Given Date

@mohitg11

 

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


Bdays.png

Super User
Super User

Re: Calculating Age on a Given Date

mohitg11 Frequent Visitor
Frequent Visitor

Re: Calculating Age on a Given Date

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!

Super User
Super User

Re: Calculate age as of a specified date

@mohitg11

 

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

aged.png


mohitg11 Frequent Visitor
Frequent Visitor

Re: Calculate age as of a specified date

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

 

Your solution worked, thanks!