Add years from variable to a date

[LMSReportsHelp]

I am helping provide a security form wherein security is provided for N years (dependant on the security type).  My data entries are for "last completed" security approval and I need to add Completion+N (we set 0 for non-renewing security) to the form.

Basics I have checked:

* Number or Date format setting(s) on column(s) - ALL columns involved

* Hidden spaces in my matching IDs/connecting columns 

I have a SECURITYTABLE  

Security ID	Years
Text	attempted whole number and decimal number - same thing
WKK123	0
WKK321	1
WKK333	3

I have this table linked to my other table by "Security ID"

I then have my PRIMARYTABLE listing users, Security, and Date completed (and a bunch of other data, but I'm trying to keep it simple).

User Id	Security ID	Completion Date	Expiration Date (Trying to ADD)
Text	Text	Date (mm/dd/yyyy)	Date (mm/dd/yyyy)
ABCD	WKK333	05/04/2023
BBBA	WKK123	10/15/2022
BBBA	WKK321	10/15/2022

First I tried a simple "Date" column addition 

Expiration Date=DATE(YEAR(PRIMARYTABLE[Completion Date])+LOOKUPVALUE(SECURITYTABLE[Years],SECURITYTABLE[Security ID],PRIMARYTABLE[Security ID])),MONTH(PRIMARYTABLE[Completion Date]),DAY(PRIMARYTABLE[Completion Date]))

I Googled and found DATEADD so I tried that instead

Expiration Date=DATEADD(PRIMARYTABLE[Completion Date],LOOKUPVALUE(SECURITYTABLE[Years],SECURITYTABLE[Security ID ],PRIMARYTABLE[Secuirty ID]),YEAR)

RANDOM rows have data.  And I mean Random. All 0's and SOME 1's.  I even added the "Lookup" column to see if there was ANY pattern.  I feel like I am losing my mind.

[MFelix · Accepted solution]

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

[MFelix · Accepted solution]

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

[LMSReportsHelp]

Ok, but can Power Query reference the 2nd table's column?  I need it to pull the variable, I am a novice with Power Queries and I didn't think they could do that.  Am I wrong?

[MFelix · Accepted solution]

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