Create ageRange column derived from a date column in the table

[Anonymous]

I have a users table which has their date of birth.  I'm trying to add a column that assigns them a DoB range based on their entered DoB.

I've read a number of community documents that handle similar setup, but I can't get it to work.  And, I'm not sure I'm approaching this in the best manner.

I attempted to create a new custom column as I transform the data and add the following:

ageRange =

VAR _18YearsOld = Date.AddYears(#date(TODAY(), -18)
VAR _25YearsOld = Date.AddYears(#date(TODAY(), -25)
VAR _35YearsOld = Date.AddYears(#date(TODAY(), -35)
VAR _45YearsOld = Date.AddYears(#date(TODAY(), -45)
VAR _55YearsOld = Date.AddYears(#date(TODAY(), -55)
VAR _65YearsOld = Date.AddYears(#date(TODAY(), -65)

RETURN
IF('users'[dateOfBirth] >= _18YearsOld <= _25YearsOld; "18-25";
IF('users'[dateOfBirth] > _25YearsOld <= _35YearsOld; "26-35";
IF('users'[dateOfBirth] > _35YearsOld <= _45YearsOld; "36-45";
IF('users'[dateOfBirth] > _45YearsOld <= _55YearsOld; "46-55";
IF('users'[dateOfBirth] > _55YearsOld <= _65YearsOld; "56-65";
IF('users'[dateOfBirth] > _65YearsOld; "65+";
"Unknown"))))))

I'm getting a weird 'Token Eof expected error - which I think is another issue.  But I wanted to first check to see if I was on the right path to creating a new date range column.  I'm not sure this is an efficient way to approach this - we will end up with a 1M+ rows in this table.

Any help would be appreciated from some experts out there.

[mahoneypat · Accepted solution]

I would first calculate the Age in query instead.  Those variables is a lot of extra calculation with that many rows.  Here is a query that does that with some example DOBs.  You can paste it into a blank query to see how it is done.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcpBCgAgCETRu7gOxiytziLe/xoNyF89+Jkywd5VqZFiMOI1FhZMtbGxidlwOLdoBII4LlUf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DOB = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DOB", type date}}),
#"Inserted Age" = Table.AddColumn(#"Changed Type", "Age", each Date.From(DateTime.LocalNow()) - [DOB], type duration),
#"Calculated Total Years" = Table.TransformColumns(#"Inserted Age",{{"Age", each Duration.TotalDays(_) / 365, type number}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Calculated Total Years",{{"Age", Int64.Type}})
in
#"Changed Type1"

Once you load that table, you can write a DAX expression like this one to put the ages into age bands in a calculated column:

Age Range =
SWITCH (
TRUE (),
DOB[Age] < 18, "<18",
DOB[Age] < =25, "18-25",
DOB[Age] <= 35, "26-35",
DOB[Age] <= 4525, "36-45",
DOB[Age] <= 55, "46-55",
DOB[Age] <= 65, "56-65",
"65+"
)

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Icey]

Hi @Anonymous ,

Is this problem solved?

Best Regards,

Icey

[amitchandak · Accepted solution]

@Anonymous ,

you should try like this in the table having column dateOfBirth

New column =

Var _age = datediff('users'[dateOfBirth],today(),Year)

Return

switch( True();
_age <= 25; "18-25";
_age <= 35; "26-35";
_age <= 45 "36-45";
_age <= 55; "46-55";
_age <= 65; "56-65";
_age > 65; "65+";
"Unknown")

Also refer

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/

[mahoneypat · Accepted solution]

I would first calculate the Age in query instead.  Those variables is a lot of extra calculation with that many rows.  Here is a query that does that with some example DOBs.  You can paste it into a blank query to see how it is done.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcpBCgAgCETRu7gOxiytziLe/xoNyF89+Jkywd5VqZFiMOI1FhZMtbGxidlwOLdoBII4LlUf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DOB = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DOB", type date}}),
#"Inserted Age" = Table.AddColumn(#"Changed Type", "Age", each Date.From(DateTime.LocalNow()) - [DOB], type duration),
#"Calculated Total Years" = Table.TransformColumns(#"Inserted Age",{{"Age", each Duration.TotalDays(_) / 365, type number}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Calculated Total Years",{{"Age", Int64.Type}})
in
#"Changed Type1"

Once you load that table, you can write a DAX expression like this one to put the ages into age bands in a calculated column:

Age Range =
SWITCH (
TRUE (),
DOB[Age] < 18, "<18",
DOB[Age] < =25, "18-25",
DOB[Age] <= 35, "26-35",
DOB[Age] <= 4525, "36-45",
DOB[Age] <= 55, "46-55",
DOB[Age] <= 65, "56-65",
"65+"
)

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Thanks @mahoneypat. for the help.  I can see this works, I did go with the simple option provided below as it will be easier to read for someone else adding to this report.