Add years from variable to a date

Hi LMSReportsHelp ,

Looking at the example is difficult to pin point what can be the error, if the table are related instead of using the LOOKUPVALUE use the RELATED function this would be something similar to:

Expiration Date =
DATEADD (
    PRIMARYTABLE[Completion Date],
    RELATED ( SECURITYTABLE[Years] ),
    YEAR
)

However this type of transformation is better achieved in the Power query in this case you can do a merge between both tables based on the Security ID and then add a column with the following syntax:

Date.AddYears ([Date], [Years])

Hi LMSReportsHelp ,

The Power Query can do it using a merge table please follow the steps below:

• Do a merge from the Primary to the security table

• Expand the Year column

• Add the new column

See full code for this below:

// Primary
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRydlHSUQr39jY2NgYyDEz0DUz1jQyMjJVidaKVnJycHCHShkYgaUNTfUMDkLQRmrSxkSGadCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, #"Security ID" = _t, #"Completion Date" = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"User ID", type text}, {"Security ID", type text}, {"Completion Date", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Security ID"}, SECURITY, {"Security ID"}, "SECURITY", JoinKind.LeftOuter),
    #"Expanded SECURITY" = Table.ExpandTableColumn(#"Merged Queries", "SECURITY", {"Years"}, {"Years"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded SECURITY",{{"Years", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Expiration Date", each Date.AddYears ([Completion Date], [Years]))
in
    #"Added Custom"

// SECURITY
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvf2NjQyVtJRMlCK1QFzjY0MgVxDONcYJGusFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Security ID" = _t, Years = _t])
in
    Source