Create a custom column to get month value

[im_newbie_123]

I have two columns (Hire Date and Current Date)

I have to find date difference between the two columns using the formula :

(Current Date - Hire Date)/12 = Tenure (new column) 

Example :

Current Date 

10/23/2019

Hire Date 

10/23/2018

Tenure

1.0

[v-frfei-msft · Accepted solution]

Hi @im_newbie_123 ,

To create a custom column as below.

((((Date.Year([Current Date]) - Date.Year([#"Hire Date "]))-1)*12)+(12-Date.Month([#"Hire Date "]))+(Date.Month([Current Date])))/12

Also we can make it dynamicly by using DateTime.LocalNow().

((((Date.Year(DateTime.LocalNow()) - Date.Year([#"Hire Date "]))-1)*12)+(12-Date.Month([#"Hire Date "]))+(Date.Month(DateTime.LocalNow())))/12

Full code for your reference as well.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ3NNA3MlHSAXEsoJzYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Current Date" = _t, #"Hire Date " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Current Date", type date}, {"Hire Date ", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each ((((Date.Year(DateTime.LocalNow()) - Date.Year([#"Hire Date "]))-1)*12)+(12-Date.Month([#"Hire Date "]))+(Date.Month(DateTime.LocalNow())))/12),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each ((((Date.Year([Current Date]) - Date.Year([#"Hire Date "]))-1)*12)+(12-Date.Month([#"Hire Date "]))+(Date.Month([Current Date])))/12)
in
    #"Added Custom1"

[Nathaniel_C]

Hi @im_newbie_123 

In Power Query = Table.AddColumn(#"Changed Type", "Custom", each ([Current Date]-[Hire Date])/365)
Go to Power Query, Select Add Column, Custom Column and fill in the code.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel