I am trying to calculate difference between two columns in two separate tables, in a new column (a measure would be fine too)
Column 1 - REFUNDS_TRACKING[RequestDate]
Column 2 - REFUNDS_PRODUCTS[OrderDate]
let’s call the difference between two columns = ReqDays
I have the calculation below on two columns in the same table to calculate days in between.
= DATEDIFF( REFUNDS_TRACKING[ReturnDate], REFUNDS_TRACKING[RefundDate], DAY )
but this does not work in this case.
Well, it would be really really helpful for our forum members to assist you in a better and efficient manner if you could upload the working of your PBIX file because without that it involve lot of guess for us. If the file contains the sensitive data then you can mask the data as well. I’m providing a link of a video below which was created by one of our expert member @BrianJ about to how mask the sensitive data.
Also, just to get you started. Previously similar sort of queries were asked as well. So I’m also providing a link of that about how the solution was achieved.
Note:Please click onto the link in order to view the entire post and not onto the “Expand/Collapse” button.
Hello @nipunadv , from what I can see you are on a good path to find the solution to your problem.
So I’m just going to get myself to explain how this function works
DATEDIFF >> Returns the count of the interval limits crossed between two dates.
Syntax
DATEDIFF (<Start_Date>, <End_Date>, )
Parameters
Start_Date >> A scalar date and time value.
End_Date >> A scalar date and time value Return value.
Interval >> The interval to be used when comparing dates. The value can be one of the following: Second, Minute, Hour, Day, Week, Month, Quarter and Year.
Return value
The count of crossed interval limits between two dates.
Comments
An error will be returned if Start_Date is greater than End_Date
However, taking into account the image below, the formula would look like this.
ReqDays = DATEDIFF( Table[OrderDate], Table[RequestDate], DAY )
Thank you for the response. I think the problem is simpler than I explained it.
Yes, this is the formula {ReqDays = DATEDIFF( Table[OrderDate], Table[RequestDate], DAY )} I used to calculate the difference between two days and it works perfectly fine. This works perfectly, if I’m trying to find the difference between two columns in the same table. Let’s call this Table1.
I want to create a new column on Table1, this time I want to get the difference between a column in Table1 and another table (Table2). So when I write the formula like
ReqDays = DATEDIFF( Table1[OrderDate], Table2[RequestDate], DAY)
it gives me these errors
Thanks for your response. I have about 20 tables in my file That’s why I didn’t attach it. I have a feeling I’m making it sound way more complicated than it is. I responded again below with a different explanation. Hope I am making it clear this time
Since your date fields are in two separate tables. You can try out this formula but there’s an condition. In my file I’ve created a common field known as “ID” and based on that I created a “One-to-One relationship” between these two tables with “Bi-Directional” filter. Below is the screenshot provided for the reference of the data model -
After this I created a “Calculated Column” in the Table 1 i.e. Order Date table. Below is the formula provided for the reference -
Dates Difference =
DATEDIFF( 'Order Date - 1'[Order Date] , RELATED( 'Request Date'[Request Date] ) , DAY )
So now the end result looks like this. Below is the screenshot provided for the reference -
I’m also attaching the working of the Excel as well as PBIX file for the reference.
Hoping you find this useful and meets your requirements which you’ve mentioned above.
That’s why I didn’t attach it. I have a feeling I’m making it sound way more complicated than it is. I responded again below with a different explanation. Hope I am making it clear this time 
