Need to subtract two date columns that are in different tables/queries

[akhaliq7]

Hi I have very basic knowledge of dax but need to subtract two date fields that are stored in two different queries/tables. A calculated column is what I want to use as I want it to be part of my data model. 

Here is an example of it in SQL:

Trunc(table1.datecolumn) - Trunc(table2.datecolumn)

I want to store the column in table2 thanks

[tamerj1]

Hi Khalid
Can you provide more details about your tables? Any common or related columns between the two tables? Do both tables have the same number of rows? 

[akhaliq7]

table2 is a fact table and table1 a dim table therefore they don't have the same number of rows, saleID is used to connect both tables

[tamerj1 · Accepted solution]

Then if the calculated column is in table2 then you can use RELATED to grap the date value from table1 so you can just sbtract Table2[Date] - RELATED ( Table1[Date] )
However if the calculated column is in table1 the you need to provide some kind of aggregation like MAX or MIN. Example: Table1[Date] - MAX ( RELATEDTABLE ( Table2[Date] ) )

[akhaliq7]

That makes sense with the aggregation happening only in the dimension table. Thanks also I got the result I was looking for.

[akhaliq7]

Thanks for your help

[amitchandak]

@akhaliq7 , You can do that using a common dimension

This is an example for direct query but true for import mode too

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

[akhaliq7]

Thats not what I am looking for.