Forum Discussion

Swilson2112's avatar
Swilson2112
Frequent Visitor
4 years ago
Solved

Compare closest date between 2 tables

EXAMPLE of tables and desired result.   TABLE 1                                        Transaction #.      Part#                           Invoice Date. 1.                             A          ...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    v-yingjl
    Community Support
    4 years ago

    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's avatar
      Swilson2112
      Frequent Visitor
      4 years ago

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