Calculating Date difference between two columns from different tables

[Anonymous]

Hi,

I want to calculate the date difference between the dates from two different tables.

Table1 Name/Column name: Deal/Deal_closedate

Table2 Name/Column name: Contact/Contact_createdate

I want to calculate the difference between the columns Deal_closedate and Contact_Createdate. I have created a measure which is as below: 

GetDateRange =

VAR a =

MAX ( 'Deal'[Deal_closedate].[Date])VAR sdate =

CALCULATE ( MAX ( 'Deal'[Deal_closedate].[Date] ), 'Deal'[Deal_closedate] = a )

VAR edate =

CALCULATE (

MAX ( 'Contact'[Contact_createdate].[Date] ),

FILTER ( 'Contact', 'Contact'[Contact_createdate] = a )

)

VAR Ddiff =

DATEDIFF ( sdate, edate, DAY )

RETURN

Ddiff

After saving this measure and pulling it to the table as shown in the screenshot, the data simply doesn't load up even after one hour (Snap1.png). I wanted to know if there is any mistake in the above expression. If yes, what can be appended so that I can calculate Deal_closedate minus - Contact_createdate?

Thankyou,

Ambarish

[Anonymous]

Thanks for the response, Amit.

So, in this scenario there are two tabels: Deal and Contact. The column name for Deal table is Deal_closedate and column name for Contact table is Contact_createdate. To calculate the difference between Deal_closedate and Contact_createdate what can the expression be written as?

[Anonymous]

Hi @Anonymous ,

Could you please provide some sample data in table Deal and Contact? Is there any relationship created between these two tables? If yes, please provide the related relationship info. Thank you.

1. Sample data

table Deal

Col1	...	Deal_closedate
xx	xx	xx
xx	xx	xx

table Contact

Col1	...	Contact_createdate
xx	xx	xx
xx	xx	xx

2. Relationship

Relation fields

Cardinality: 1:*?

Cross filter direction: Single/ Both

In addition, you can refer the method in the following links to get the differents of two dates from different tables.

Date Difference between 2 different tables

Power bi Date Difference – 8 Different Examples

Best Regards

[amitchandak]

@Anonymous , You have forced a context of common dimension. say contract

example

refer if needed

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

VAR Ddiff =

AverageX(Values(Contract[Contract]), DATEDIFF ( sdate, edate, DAY ))