Forum Discussion
Date Difference between dates from multiple tables
HI,
I have 2 tables ( Orders, Tasks) with relationship Many-to-Many and Single Direction Filter, from Tasks=>Orders.
I need to calculate the difference between Order Date and the Task date. I've been using Datediff and Calculate, but didn't managed to get correct results. Can anyone help me with that?
Orders Table
| Order ID | User ID | Product | Order Date | Order Status |
| 1 | 2689105 | Prod 1 | 10/6/2020 | Completed |
| 2 | 2689105 | Prod 2 | 10/1/2020 | Cancelled |
| 3 | 1052689 | Prod 3 | 9/6/2020 | Completed |
| 4 | 5268910 | Prod 4 | 7/12/2020 | Completed |
Tasks Table
| Task ID | User ID | Task Type | Task Date |
| 1 | 2689105 | Type 1 | 10/5/2020 |
2 | 2689105 | Type 2 | 9/18/2020 |
| 3 | 1052689 | Type 1 | 7/20/2020 |
| 4 | 5268910 | Type 2 | 8/12/2020 |
| 5 | 5268910 | Type 1 | 10/1/2020 |
| 6 | 4356744 | Type 1 | 6/25/2020 |
Key field is "User ID" . The Order dates are also filtered by a set of products , in this example "Prod 1" and "Prod 3", and the Tasks Table is filtered by Task Type, in this example "Type 1".
Expected result
| Order ID | User ID | Product | Order Date | Task Date | DateDiff | Order Status |
| 1 | 2689105 | Prod 1 | 10/6/2020 | 10/5/2020 | 1 Day | Completed |
| 3 | 1052689 | Prod 3 | 9/6/2020 | 7/20/2020 | 48 Days | Completed |
Thank you!
Hi Anonymous，
First create 3 slicer tables as below:
ID slicer = VALUES('Tasks Table'[User ID])Product slicer = VALUES('Orders Table'[Product])Type slicer = VALUES('Tasks Table'[Task Type])Then create 4 measures as below:
_Product = CALCULATE(MAX('Orders Table'[Product]),'Orders Table'[Product] in FILTERS('Product slicer'[Product]))_User ID = CALCULATE(MAX('Orders Table'[User ID]),'Orders Table'[User ID] in FILTERS('ID slicer'[User ID]))_Task date = CALCULATE(MAX('Tasks Table'[Task Date]),FILTER('Tasks Table','Tasks Table'[User ID]=[_User ID]&&'Tasks Table'[Task Type] in FILTERS('Type slicer'[Task Type])))_Diff = var _diff= DATEDIFF([_Task date],MAX('Orders Table'[Order Date]),DAY) Return IF(_diff<=1,_diff&" day",_diff&" days")And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
4 Replies
Anonymous , You need to have a common user ID table.
Then you can have
datediff(min(orders[Order Date]), max(Tasks[task Date]), Day)
refer
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.Hi Anonymous ,
Why there is no data about 5268910 in your expected result?How to match "Product" and "Task Type"?
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
v-kelly-msft as the data has the filters, basically 5268910 is excluded by the filter, therefore, the result will contain blank values for date diff.
Hi Anonymous，
First create 3 slicer tables as below:
ID slicer = VALUES('Tasks Table'[User ID])Product slicer = VALUES('Orders Table'[Product])Type slicer = VALUES('Tasks Table'[Task Type])Then create 4 measures as below:
_Product = CALCULATE(MAX('Orders Table'[Product]),'Orders Table'[Product] in FILTERS('Product slicer'[Product]))_User ID = CALCULATE(MAX('Orders Table'[User ID]),'Orders Table'[User ID] in FILTERS('ID slicer'[User ID]))_Task date = CALCULATE(MAX('Tasks Table'[Task Date]),FILTER('Tasks Table','Tasks Table'[User ID]=[_User ID]&&'Tasks Table'[Task Type] in FILTERS('Type slicer'[Task Type])))_Diff = var _diff= DATEDIFF([_Task date],MAX('Orders Table'[Order Date]),DAY) Return IF(_diff<=1,_diff&" day",_diff&" days")And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
