Date comparison beetwen 2 tables

[szczepaneq]

Hello,

In my model I have 2 tables with purchase orders (from 2 different databases) and I need to compare Delivery dates.

I think, it's more complicated because, each purchase order has multiple lines of sale with different dates. For lets say PO 1234 we could have completely different dates for each line. Moreover, different amount of lines (not common).

I have relations based on Purchase order (many to many). Unfortunately, I don't have ID column on this tables (should I add it?).

First of all, when I put data in table or matrix viz (Purchase order[table1], Delivery date[table1], Delivery date[tables2]) I have all possible combinations. If PO 1234 has 2 lines in table1 and 2 lines in table 2 with different dates -> then I see 4 rows in my visualitization. I'm able to compare quantities or amount of lines.

If i try to use DATE.DIFF with RELATED as column or measure, it's not possible (don't recognize column from related table etc.)

I'm beginner and I will appreciate any suggestions. I was trying answer in existing topics but I didn't find. Thanks.

[v-jayw-msft · Accepted solution]

Hi @szczepaneq ,

Create a many to many relationship between two tables using [Purchase order]. But as you said if there's two rows in table1 and two rows in table2 and you don't have any other rows to identify the records, so there would be four rows to be shown.

Best Regards,

Jay

[szczepaneq]

I have created new column "PO line" (based on chronological date) and with relation 1:1 the comparison is possible (using DATEDIFF function). Thanks.

[amitchandak]

@szczepaneq , do you have purchase order no, Line no, which can act as join to get all the dates ??

[szczepaneq]

I have only Purchase Order no (which is duplicated if the Purchase order has >1 sale line) and any column like "line no". In both tables.

As below: