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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rushil_15
Frequent Visitor

Get age in years and month from birthdate

Hello,

 

I am working on one data set where I have birthdate and I am looking to get AGE in years and months. using power query I was able to get the age in the year but is there any way to get both? Thanks in advance

2 ACCEPTED SOLUTIONS

Thanks for providing the additional information. Based on that, you can modify the formula provided earlier to calculate the age in years and months as follows:

 

= let

birthdate = [birthdate],

now = DateTime.LocalNow(),

ageInDays = Duration.Days(now - birthdate),

ageInYears = Number.RoundDown(ageInDays / 365),

ageInMonths = Number.RoundDown((ageInDays - (ageInYears * 365)) / 30),

ageText = Text.Combine({Text.From(ageInYears), " years, ",

Text.From(ageInMonths), " months"})

in

ageText

 

This formula first calculates the duration between the birthdate and the current date in days. It then calculates the age in years by dividing the duration by 365 and rounding down to the nearest whole number. Next, it calculates the age in months by subtracting the number of years from the total duration in days, dividing by 30, and rounding down to the nearest whole number. Finally, it combines the age in years and months into a single text string.

 

To add this formula as a custom column in Power Query for the "master_serviceplan" table:

 

  1. Open the "master_serviceplan" table in Power Query Editor.
  2. Click the "Add Column" tab in the ribbon.
  3. Click "Custom Column" to open the "Add Custom Column" dialog box.
  4. Enter "Age in Years and Months" as the new column name.
  5. Paste the formula provided above into the "Custom Column Formula" box.
  6. Click "OK" to create the new column.

 

Now you should see a new column in your "master_serviceplan" table that displays the age in years and months for each row.

 

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly.

View solution in original post

You are welcome @Rushil_15.

If my solution worked, then please Accept it as the solution and give Kudos to help the other members find it more quickly.

 

 

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Rushil_15
Frequent Visitor

Thank you so much 

You are welcome @Rushil_15.

If my solution worked, then please Accept it as the solution and give Kudos to help the other members find it more quickly.

 

 

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly.

ichavarria
Solution Specialist
Solution Specialist

Hi @Rushil_15,

 

With the limited information you provided, this is one way you could do it:

 

  1. Create a new column in Power Query by clicking on the "Add Column" tab, and then selecting "Custom Column."

  2. In the "Custom Column" dialog box, enter a name for the new column, such as "Age in Years and Months."

  3. In the "Custom Column" dialog box, enter the following formula to calculate the age in years and months:

= Duration.FromDays(Number.From(DateTime.LocalNow())-Number.From([Birthdate])) / #duration(365,0,0,0) & " years, " & Duration.FromDays(Number.From(DateTime.LocalNow())-Number.From([Birthdate])) / #duration(30,0,0,0) & " months"

 

This formula calculates the duration between the birthdate and the current date, and then converts it to years and months. The "#duration" function is used to define the length of a year and a month.

 

4. Click "OK" to create the new column.

 

Now you should see a new column in your Power Query table that displays the age in years and months for each row.

 

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly.

Hello,

Thanks for your time and quick follow-up. Below is the screenshot of the dataset (hiding because some have PII's). but the table name is master_serviceplan. 

Let me know if that works!

BI_Help.png

Thanks for providing the additional information. Based on that, you can modify the formula provided earlier to calculate the age in years and months as follows:

 

= let

birthdate = [birthdate],

now = DateTime.LocalNow(),

ageInDays = Duration.Days(now - birthdate),

ageInYears = Number.RoundDown(ageInDays / 365),

ageInMonths = Number.RoundDown((ageInDays - (ageInYears * 365)) / 30),

ageText = Text.Combine({Text.From(ageInYears), " years, ",

Text.From(ageInMonths), " months"})

in

ageText

 

This formula first calculates the duration between the birthdate and the current date in days. It then calculates the age in years by dividing the duration by 365 and rounding down to the nearest whole number. Next, it calculates the age in months by subtracting the number of years from the total duration in days, dividing by 30, and rounding down to the nearest whole number. Finally, it combines the age in years and months into a single text string.

 

To add this formula as a custom column in Power Query for the "master_serviceplan" table:

 

  1. Open the "master_serviceplan" table in Power Query Editor.
  2. Click the "Add Column" tab in the ribbon.
  3. Click "Custom Column" to open the "Add Custom Column" dialog box.
  4. Enter "Age in Years and Months" as the new column name.
  5. Paste the formula provided above into the "Custom Column Formula" box.
  6. Click "OK" to create the new column.

 

Now you should see a new column in your "master_serviceplan" table that displays the age in years and months for each row.

 

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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