topic Re: Calculating Relative Date of Birth in DAX Commands and Tips

Calculating Relative Date of Birth

bonitatem — Fri, 07 Jun 2024 15:22:52 GMT

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!

Re: Calculating Relative Date of Birth

Greg_Deckler — Fri, 07 Jun 2024 15:28:41 GMT

bonitatem So like:

Dynamic Age Measure = VAR __MaxDate = MAX( 'Dates'[Date] ) VAR __BD = MAX( 'People'[dob] ) VAR __Result = YEAR( __MaxDate ) - YEAR( __BD ) RETURN __Result

Re: Calculating Relative Date of Birth

bonitatem — Fri, 07 Jun 2024 19:15:25 GMT

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.

Age_Band =

DATATABLE(

"Age Band",STRING,

"Sort Order",INTEGER,

"Start",INTEGER,

"End",INTEGER,

{

{"<18",1,0,17},

{"18-29",2,18,29},

{"30-39",3,30,39},

{"40-49",4,40,49},

{"50-59",5,50,59},

{"60-69",6,60,69},

{"70-79",7,70,79},

{"80-89",8,80,89},

{"90+",9,90,200}

}

)

Age_Count_Per_Band =

var _currAgeBandStart=SELECTEDVALUE('Age_Band'[Start])

var _currAgeBandEnd=SELECTEDVALUE('Age_Band'[End])

var _currAgeList=GENERATESERIES(

_currAgeBandStart,_currAgeBandEnd,1)

RETURN

CALCULATE(

COUNTROWS('people'),

TREATAS(_currAgeList,'people'[Age]),

FILTER(people,[InDateRange_Agency_Enrollment]=1),

FILTER(program_enrollment,[InDateRange_Program_Enrollment]=1),

FILTER(enrollment,[InDateRange_Facility_Enrollment]=1)

)

Re: Calculating Relative Date of Birth

bonitatem — Fri, 07 Jun 2024 19:18:43 GMT

Forgot to include you: Greg_Deckler

Re: Calculating Relative Date of Birth

Greg_Deckler — Sat, 08 Jun 2024 15:50:13 GMT

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

Re: Calculating Relative Date of Birth

Anonymous — Mon, 10 Jun 2024 05:58:17 GMT

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