topic Re: Need to subtract two date columns that are in different tables/queries in DAX Commands and Tips

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

akhaliq7 — Thu, 03 Mar 2022 10:48:50 GMT

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

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

amitchandak — Thu, 03 Mar 2022 12:03:39 GMT

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-Difference-Across/ba-p/934397#M451

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

akhaliq7 — Thu, 03 Mar 2022 12:22:10 GMT

Thats not what I am looking for.

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

tamerj1 — Thu, 03 Mar 2022 12:37:35 GMT

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?

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

akhaliq7 — Thu, 03 Mar 2022 12:42:25 GMT

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

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

tamerj1 — Thu, 03 Mar 2022 12:50:57 GMT

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] ) )

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

akhaliq7 — Thu, 03 Mar 2022 13:50:17 GMT

Thanks for your help

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

akhaliq7 — Thu, 03 Mar 2022 15:35:06 GMT

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