Date Difference between dates from multiple tables

[Anonymous]

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!

[v-kelly-msft]

Hi  @Anonymous ,

Why there is no data about 5268910 in your expected result?How to match "Product" and "Task Type"?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@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.

[v-kelly-msft · Accepted solution]

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,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@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

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.