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




Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
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