Compare closest date between 2 tables

[Swilson2112]

EXAMPLE of tables and desired result.

TABLE 1                                       

Transaction #.      Part#                           Invoice Date.

1.                             A                              02/14/2022

2.                             A                              02/02/2022

TABLE 2

Part#                            Change Date.           Change Amount

A.                                   02/10/2022.                1.25

A                                    02/05/2022                 0.37

A                                    01/15/2022                 2.56

Desired Resultes

Transaction   Return Result.     Notes:

1                      1.25                Chnage date of 02/10/2022 is closest less than date from invoice date of 02/14/2022 

2                      2.56                Chnage date of 01/15/2022 is closest less than date from invoice date of 02/02/2022

Both tables have millions of rows of data and a unique part may have over 1000 rows with various change dates and change amounts.  I am looking to return only the change amount from the row in table 2 with the closest less than change date compared to the invoice date from Table 1 for that specific part#. 

I thank you for all your help with this.

[Anonymous]

OK, so i now have a merged table with all the proper columns. 

Invoice#, Invoice Date, Changed Date, Change Cost

I would like to create a measure that I can insert into my visual that will select the "Change Cost" for the invoice selected and when the Changed date is the closest less then or equal to the Invoice date.

I was thinking of createing a virtual table in a measure using the calculate table function but it is not allowing me to use the proper filters.

Thanks again for all your help,

Now I am trying to create a measuer that will return the proper cost based on the following:

[v-yingjl]

Hi @Anonymous ,
You can try to create a measure like this to get the result:

Return Result =
VAR merge =
    ADDCOLUMNS (
        FILTER (
            NATURALINNERJOIN (
                SELECTCOLUMNS (
                    'Table1',
                    "Transaction #.", 'Table1'[Transaction #.],
                    "Part#", "" & 'Table1'[Part#],
                    "Invoice Date.", 'Table1'[Invoice Date.]
                ),
                SELECTCOLUMNS (
                    'Table2',
                    "Part#", 'Table2'[Part#] & "",
                    "Changed Date.", 'Table2'[Change Date],
                    "Change Amount", 'Table2'[Change Amount]
                )
            ),
            [Changed Date.] < [Invoice Date.]
        ),
        "DIFF", DATEDIFF ( [Changed Date.], [Invoice Date.], DAY )
    )
RETURN
    MAXX (
        FILTER (
            merge,
            [Transaction #.]
                IN DISTINCT ( 'Table1'[Transaction #.] )
                    && [DIFF]
                        = MINX (
                            FILTER (
                                merge,
                                [Transaction #.]
                                    IN DISTINCT ( 'Table1'[Transaction #.] )
                                        && [Part#] IN DISTINCT ( 'Table1'[Part#] )
                            ),
                            [DIFF]
                        )
        ),
        [Change Amount]
    )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Swilson2112]

THANK YOU, THANK YOU, THANK YOU.  This worked perfectly...

[Anonymous]

Wow Alexis, I think that this one might be over my head.  I did try and merge the two tables together, but each table contain more than 50 colums of data that I am using within the visuals and when I merge them the visuals get squirrely.  I was hoping that ther was a simple way of adding a measure that filters on table 1 part and date and then reaches out to table 2 to back and find the closest before date to return the proper cost. I'll keep on it.  Thank you.

[AlexisOlson · Accepted solution]

What I suggested only adds two columns to Table1 and one of them (the table-valued column) can be removed after the new custom column has been defined. Create a new blank query and paste the M code I provided over the existing code in the Advanced editor to examine the applied steps to understand more easily.

You can also do this purely in DAX without doing any table merges but you asked in the Power Query forum so I have a Power Query answer. You can also add the custom column in Power Query without doing a merge first but I think it might be too slow given your millions of rows.

[AlexisOlson]

I'd probably merge Table2 onto Table1 matching on Part# to get the relevant rows and then filter those rows to the ones before the invoice and then take the maximal change date row from the remaining filtered rows.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYiN9QxN9IwMjI6VYnWglI7ioEVQwFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Transaction #" = _t, #"Part#" = _t, #"Invoice Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction #", Int64.Type}, {"Part#", type text}, {"Invoice Date", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Part#"}, Table2, {"Part#"}, "Table2", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Return Result",
        (r) => Table.Max(
                   Table.SelectRows(
                       r[Table2],
                       each [Change Date] <= r[Invoice Date]
                   ),
                   "Change Date"
               )[Change Amount],
        type number)
in
    #"Added Custom"

[Anonymous]

Thank you for that.  Ya, I was looking to do this strickly with DAX.  I'm new to this forum and also to PowerBI and I apologize for tagging this as a Power Querry issue.