Age of user - calculated column Power Query

[piotrgrendus87]

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:



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

[BeaBF · Accepted solution]

@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]

@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/