cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Create ageRange column derived from a date column in the table

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Create ageRange column derived from a date column in the table

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

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Create ageRange column derived from a date column in the table

@iclarkie99 ,

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/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
Highlighted
Community Champion
Community Champion

Re: Create ageRange column derived from a date column in the table

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

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Create ageRange column derived from a date column in the table

@iclarkie99 ,

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/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Community Support
Community Support

Re: Create ageRange column derived from a date column in the table

Hi @iclarkie99 ,

 

Is this problem solved?

 

 

Best Regards,

Icey

Highlighted
Frequent Visitor

Re: Create ageRange column derived from a date column in the table

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.

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Kudoed Authors