cancel
Showing results for
Search instead for
Did you mean:
Highlighted
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

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

6 REPLIES 6
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

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

Super User

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

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

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!