Calculating (No. of days) differences between days two dates from two difference tables.

[Andrew_na_lang]

Hello Guys,

I need help! 

I have 2 columns in the table visualization that came from 2 different tables. I need to compute the number of days difference between the earliest registration_time_id and Earliest time_created_id and its average no. of days. 

 Any help will be much appreciated. 

Regards,

[amitchandak]

@Andrew_na_lang , You need to have some common dimension column

Same as this one

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

[Andrew_na_lang]

It is more confusing. 

[Anonymous · Accepted solution]

Hi @Andrew_na_lang ,

I think there should be a KeyColumn in both tables to relate them.

Table1:

Table2:

Measure:

The number of days difference between = 
VAR _MINUTE =
    DATEDIFF (
        MAX ( Table1[Earliest Registration_time_id] ),
        MAX ( Table2[Earliest Time_created_id] ),
        MINUTE
    )
RETURN
    SUMX ( VALUES ( Table1[KeyColumn] ), _MINUTE ) / 60 / 24

Average = 
AVERAGEX(VALUES(Table1[KeyColumn]),[The number of days difference between])

Result is as below.

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.