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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply

Age of user - calculated column Power Query

Hello.
I need support regarding user age calculation. I have the following data and would like to create a calculated column in PowerQuery that shows:

data.png

User_Start_DT     USER_END_DT          Age column

21/06/2019      null ( still working )       0 - 5 Years

11/07/2007          11/07/2007               0 - 5 Years

11/09/2013           30/09/2021             5 - 10 Years

28/05/2009           23/03/2015             5 - 10 Years

21/06/2011           11/01/2022            10 - 15 Years

11/07/2007      null ( still working )     10 - 15 Years

 

etc

1 ACCEPTED SOLUTION
BeaBF
Super User
Super User

@piotrgrendus87 Here the code to calculate Age Column in PQ with the sample data pasted:

 

Text.From(Number.RoundUp(
(if Number.Round(Number.From(([#"   USER_END_DT         "] - [#"User_Start_DT "])/( 365.25 )) ,0 ) = null then 1 else if Number.Round(Number.From(([#"   USER_END_DT         "] - [#"User_Start_DT "])/( 365.25 )) ,0 ) = 0 then 1 else Number.Round(Number.From(([#"   USER_END_DT         "] - [#"User_Start_DT "])/( 365.25 )) ,0 )) /5)*5-5) & " - " &
Text.From(Number.RoundUp(
(if Number.Round(Number.From(([#"   USER_END_DT         "] - [#"User_Start_DT "])/( 365.25 )) ,0 ) = null then 1 else if Number.Round(Number.From(([#"   USER_END_DT         "] - [#"User_Start_DT "])/( 365.25 )) ,0 ) = 0 then 1 else Number.Round(Number.From(([#"   USER_END_DT         "] - [#"User_Start_DT "])/( 365.25 )) ,0 )) /5)*5)

 

BBF

View solution in original post

2 REPLIES 2
BeaBF
Super User
Super User

@piotrgrendus87 Here the code to calculate Age Column in PQ with the sample data pasted:

 

Text.From(Number.RoundUp(
(if Number.Round(Number.From(([#"   USER_END_DT         "] - [#"User_Start_DT "])/( 365.25 )) ,0 ) = null then 1 else if Number.Round(Number.From(([#"   USER_END_DT         "] - [#"User_Start_DT "])/( 365.25 )) ,0 ) = 0 then 1 else Number.Round(Number.From(([#"   USER_END_DT         "] - [#"User_Start_DT "])/( 365.25 )) ,0 )) /5)*5-5) & " - " &
Text.From(Number.RoundUp(
(if Number.Round(Number.From(([#"   USER_END_DT         "] - [#"User_Start_DT "])/( 365.25 )) ,0 ) = null then 1 else if Number.Round(Number.From(([#"   USER_END_DT         "] - [#"User_Start_DT "])/( 365.25 )) ,0 ) = 0 then 1 else Number.Round(Number.From(([#"   USER_END_DT         "] - [#"User_Start_DT "])/( 365.25 )) ,0 )) /5)*5)

 

BBF

Manoj_Nair
Solution Supplier
Solution Supplier

@piotrgrendus87 - try this PBIX file

Age of user - calculated column Power Query.pbix 

Kindly let me know it this answered your question I would be happy if you could mark my post as a solution and give it a thumbs up

Best regards

Manoj Nair

Linkedin - https://www.linkedin.com/in/manoj-nair-%E2%98%81-344666104/

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.