New column returns value by looking up corresponding value from a date column + X Months

[Anonymous]

I have one table and I want to create a "New Value Column".

Each Unique Name has an "Original Month".

I want to add 36 months to the Original Month value, then look up the corresponding date from the "Lookup Month' column, then lookup the value from the "Original Value" column, and set that value as the new value  for "New Value Column" every time you see that Unique Name. 

If there is no "Original Month + 36 months", then set the "New Value Column" as null for that Unique Name.

Thank you for the help.

Name	Lookup Month	Original Month	Original Value	New Value Column
John	6/1/2017	11/20/2015	1	18
John	7/1/2017	11/20/2015	2	18
John	8/1/2017	11/20/2015	3	18
John	9/1/2017	11/20/2015	4	18
John	10/1/2017	11/20/2015	5	18
John	11/1/2017	11/20/2015	6	18
John	12/1/2017	11/20/2015	7	18
John	1/1/2018	11/20/2015	8	18
John	2/1/2018	11/20/2015	9	18
John	3/1/2018	11/20/2015	10	18
John	4/1/2018	11/20/2015	11	18
John	5/1/2018	11/20/2015	12	18
John	6/1/2018	11/20/2015	13	18
John	7/1/2018	11/20/2015	14	18
John	8/1/2018	11/20/2015	15	18
John	9/1/2018	11/20/2015	16	18
John	10/1/2018	11/20/2015	17	18
John	11/1/2018	11/20/2015	18	18
Betty	3/1/2018	11/28/2017	1	null
Betty	4/1/2018	11/28/2017	2	null
Betty	5/1/2018	11/28/2017	3	null
Betty	6/1/2018	11/28/2017	4	null
Betty	7/1/2018	11/28/2017	5	null
Betty	8/1/2018	11/28/2017	6	null
Betty	9/1/2018	11/28/2017	7	null
Betty	10/1/2018	11/28/2017	8	null
Betty	11/1/2018	11/28/2017	9	null
Betty	12/1/2018	11/28/2017	10	null
Betty	1/1/2019	11/28/2017	11	null
Betty	2/1/2019	11/28/2017	12	null
Betty	3/1/2019	11/28/2017	13	null
Betty	4/1/2019	11/28/2017	14	null
Betty	5/1/2019	11/28/2017	15	null
Betty	6/1/2019	11/28/2017	16	null
Betty	7/1/2019	11/28/2017	17	null

[v-binbinyu-msft · Accepted solution]

Hi @Anonymous ,

Please try below steps:

1. create a measure with below dax formula

Measure =
VAR cur_name =
    SELECTEDVALUE ( 'Table'[Name] )
VAR cur_om =
    SELECTEDVALUE ( 'Table'[Original Month] )
VAR cur_om_year =
    YEAR ( cur_om )
VAR cur_om_month =
    MONTH ( cur_om )
VAR tmp1 =
    FILTER ( ALL ( 'Table' ), 'Table'[Name] = cur_name )
VAR _val =
    CALCULATE (
        MAX ( [Original Value ] ),
        FILTER (
            tmp1,
            YEAR ( [Lookup Month] ) = cur_om_year + 3
                && MONTH ( [Lookup Month] ) = cur_om_month
        )
    )
RETURN
    IF ( ISBLANK ( _val ), "null", _val )

2. add a table visual with fields and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.