Forum Discussion
Create a custom column to get month value
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
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"
2 Replies
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.
NathanielHi 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"
