Forum Discussion
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.
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)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"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.
6 Replies
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.
- AlexisOlson
Super User
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.
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.
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)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"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.
