Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I'm trying to do some age analysis for a group of people and created an age column in Power Query based on the date of birth, but the ages are static based on today's date and I need them to be dynamic based on my date table. For instance, if the user is slicing to see the 2022 population I need the ages to represent each person's age in 2022, not their current age.
How can I determine the age dynamically so that I can use it for calculations like average age, min age, and max age, and also do an age distribution? My date of birth is located in my people[dob] column and the date is Date[Date].
Thank you!
Solved! Go to Solution.
@bonitatem Well, you could do something like this:
Count of Age Band =
VAR __Start = MAX( 'Age_Band'[Start] )
VAR __End = MAX( 'Age_Band'[End] )
VAR __Table = ADDCOLUMNS( 'people', "__Age", [Dynamic Age Measure] )
VAR __Result = COUNTROWS( FILTER( __Table, [__Age] >= __Start && [__Age] <= __End ) )
RETURN
__Result
Thanks for the reply from @Greg_Deckler , please allow me to provide another insight:
Hi @bonitatem ,
Here are the steps you can follow:
1. Create measure.
age distribution =
MAXX(
FILTER(ALL('Age_Band'),
[Dynamic Age Measure] >= 'Age_Band'[Start]&&[Dynamic Age Measure]<='Age_Band'[End]),[Age Band])
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
This works great for my measures - but how could I use this to do an age distribution? Currently I created a table to define my age bands and then I'm using a measure with TREATAS to create the distribution.
@bonitatem Well, you could do something like this:
Count of Age Band =
VAR __Start = MAX( 'Age_Band'[Start] )
VAR __End = MAX( 'Age_Band'[End] )
VAR __Table = ADDCOLUMNS( 'people', "__Age", [Dynamic Age Measure] )
VAR __Result = COUNTROWS( FILTER( __Table, [__Age] >= __Start && [__Age] <= __End ) )
RETURN
__Result
@bonitatem So like:
Dynamic Age Measure =
VAR __MaxDate = MAX( 'Dates'[Date] )
VAR __BD = MAX( 'People'[dob] )
VAR __Result = YEAR( __MaxDate ) - YEAR( __BD )
RETURN
__Result
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
24 | |
12 | |
11 | |
10 |