Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
@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
Thanks @Zubair_Muhammad, don't need to apologies, I'm slower in responding.
Your solution worked, thanks!
@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])))
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |