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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cfindling
Regular Visitor

Age on a specific date from DOB

I have a vaccine table that has a vaccine administered date, then I have a person table that has the person's date of birth.  I need to know the age of my patient on the date the vaccine was administered.  I figured out how to get the person's age, but the problem is it is giving me their current age, not the age they were at the time the vaccine was given which could have been last year or longer ago.  Thank you for your help.

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi @cfindling ,

 

I agreed with @AlexisOlson 's suggestion to use DATEDIFF() to calculate the Age:

Measure = DATEDIFF(MAX('person'[date of birth]),MAX('vaccine'[vaccine administered date]),YEAR)
Column = 
var _vaccine=LOOKUPVALUE('vaccine'[vaccine administered date],[person ],[person])
return DATEDIFF([date of birth],_vaccine,YEAR)

Eyelyn9_0-1636427374822.png

 

If it does not work, you may try to do it in Power Query, below is the whole M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hcu5CQAxDATAXjY2rLUnfCi8pwuh/tuwUTowmXgwYBGLJupCjcTbdDudUsvX4kGblDf9hzSneOZC1QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [person = _t, #"date of birth" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"person", type text}, {"date of birth", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"person"}, vaccine, {"person "}, "vaccine", JoinKind.LeftOuter),
    #"Expanded vaccine" = Table.ExpandTableColumn(#"Merged Queries", "vaccine", {"vaccine administered date"}, {"vaccine administered date"}),
    #"Inserted Age" = Table.AddColumn(#"Expanded vaccine", "Age", each [vaccine administered date] - [date of birth]),
    #"Calculated Total Years" = Table.TransformColumns(#"Inserted Age",{{"Age", each Duration.TotalDays(_) / 365, type number}}),
    #"Rounded Down" = Table.TransformColumns(#"Calculated Total Years",{{"Age", Number.RoundDown, Int64.Type}})
in
    #"Rounded Down"

Eyelyn9_1-1636427404298.png

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-eqin-msft
Community Support
Community Support

Hi @cfindling ,

 

I agreed with @AlexisOlson 's suggestion to use DATEDIFF() to calculate the Age:

Measure = DATEDIFF(MAX('person'[date of birth]),MAX('vaccine'[vaccine administered date]),YEAR)
Column = 
var _vaccine=LOOKUPVALUE('vaccine'[vaccine administered date],[person ],[person])
return DATEDIFF([date of birth],_vaccine,YEAR)

Eyelyn9_0-1636427374822.png

 

If it does not work, you may try to do it in Power Query, below is the whole M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hcu5CQAxDATAXjY2rLUnfCi8pwuh/tuwUTowmXgwYBGLJupCjcTbdDudUsvX4kGblDf9hzSneOZC1QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [person = _t, #"date of birth" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"person", type text}, {"date of birth", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"person"}, vaccine, {"person "}, "vaccine", JoinKind.LeftOuter),
    #"Expanded vaccine" = Table.ExpandTableColumn(#"Merged Queries", "vaccine", {"vaccine administered date"}, {"vaccine administered date"}),
    #"Inserted Age" = Table.AddColumn(#"Expanded vaccine", "Age", each [vaccine administered date] - [date of birth]),
    #"Calculated Total Years" = Table.TransformColumns(#"Inserted Age",{{"Age", each Duration.TotalDays(_) / 365, type number}}),
    #"Rounded Down" = Table.TransformColumns(#"Calculated Total Years",{{"Age", Number.RoundDown, Int64.Type}})
in
    #"Rounded Down"

Eyelyn9_1-1636427404298.png

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

If your model looks like this then it's pretty easy then this DAX will do it

AgeAtVaccination = DATEDIFF( MAX('Patient'[DOB]),MAX('Vaccination'[DateOfVaccination]),YEAR)

 

vaccinationModel.png

vtable1.png   vtable2.png

Quick and dirty example. Should be able to use it as a measure as well.

vresults.png

I must be doing something wrong.  I have tried it a couple of times, I keep getting the age as -3 for all patients if I do the column.  If I do the measure I can never get it to load, it just spins and spins.  My dob and vaccine date columns are in a date format, not a hiearchy if that makes a difference. 

FYI, the DAX is gonna be a bit different for measures and calculated columns and the way your tables are related could also make a difference. @Anonymous can help better if you specify your situation regarding these.

Anonymous
Not applicable

Are you wanting the Age at Vaccination as a Measure or a calculated column?  If all you want is a column and you are using sql queries, you can use DATEDIFF in your SQL.  If you want to do it in PowerBI there are many ways to do this.  You could do it in PowerQuery before the data is even loaded to PBI.  Do you have a relationship defined between the two tables?  Depending on your answers to these questions there are several possible solutions

I don't know that I have a strong preference between measure and column, although I am thinking probably a a measure. I would like to do it in Power BI desktop. I do have a relationship between these 2 tables. 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors