Calculating Relative Date of Birth

[bonitatem]

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!

[Anonymous]

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

[bonitatem]

Forgot to include you: @Greg_Deckler 

[bonitatem]

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)

)

[Greg_Deckler · Accepted 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

[Greg_Deckler]

@bonitatem So like:

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