Modeling three tables (only need date)

[tyan]

https://spo23-my.sharepoint.com/:x:/g/personal/a7579_365ms_vip/EUawrvBZjBREqbWwqNgGy4cB8rjM-TdIQoJ81... 

Hi. I was wondering how to build reletionship between these 3 worksheet. 

My main target is to let worksheet 1 (shipping date) and worksheet 2( shipping date) vlookup into my worksheet 3 to create a column call shipping date 1 and shipping date 2.  the reason why did this is becasue worksheet 1 and workshee2' date is not completed.  I cann't just use model number or N/O to mereg all into worksheet 3.  I use N/O & Model Number& N/o SHIPPING DATE as my search value in vlookup in excel. 

I want to use    O/C Shipping date -( shipping date 1 & shipping date 2）= date difference %   ON   my worksheet 3 

Worksheet1

Worksheet2

Worksheet 3

[tyan]

@PaulDBrown Hi thanks for the suggesution. I have insert a sharepoint link on top. I am not used to this function im not sure i can embed a excel data here. 

[v-rzhou-msft]

Hi @tyan ,

If you want to lookup value from other columns by multiple filters, you can try Merge function in Power Query.

For reference: Combine queries

But if you merge columns by N/O and Model Number and N/o SHIPPING DATE, I think you will get same N/o SHIPPING DATE1 and N/o SHIPPING DATE2 as O/C Shipping date in third table. Then the date difference will return 0.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown]

It would help if you provided sample data instead of images, but the recommended  procedure is to create a Date Table linked to the corresponding date fields in the tables. You also create dimension fields for other fields which are common to other tables.

You then use the field from the date table and from the dimension tables in slicer, visuals, filters measures...

It makes calculations much more straightforward.