Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

Reply
bonitatem
Regular Visitor

Calculating Relative Date of Birth

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!

1 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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1717999057689.png

 

 

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
Regular Visitor

Forgot to include you: @Greg_Deckler 

bonitatem
Regular Visitor

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)
)
 

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

@bonitatem So like:

 

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

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.