Difference between two dates - from two different tables

[Anonymous]

I am trying to calculate our on time performance.   Date due  vs  date shipped. 

I have a date table.   The relationship between Flu Shipped and the date table is with "Date Shipped"   and on the Orders table the relationship is with the Due Date.  

I need find the difference between these two dates.   

[v-lid-msft]

Hi @Anonymous ,

Do you want to calculate the difference between the "Flu shipped " Table and "Orders" Table? Does the "Flu shipped" Tables and "Order" Tables have relationships in logic? What is the connective column they have if they have relation, such as ID column?

Best regards,

[Anonymous]

Hi, make a new relationship on those columns and that new relationship will not be "active".

so you have to add the syntax in your measure/columns "USERELATIONSHIP"

https://docs.microsoft.com/sv-se/dax/userelationship-function-dax

and then in your measure you type this syntax: 

=CALCULATE(SUM(InternetSales[SalesAmount]), USERELATIONSHIP(InternetSales[ShippingDate], DateTime[Date]))

Hope this can help you 🙂

/Adam

[Anonymous]

Hi, 

Sorry to a noob.   But create a relationship between my date table and these?  I have that already.  

Or just create a relationhip, between the order table and the shipping table?   If so, from which field to which field?   And won't this be a many to many relationship?

[amitchandak]

I tried to create a Data model that can refer to your data model. Please find this pbix, where I created 4 ways how can you get date diff from two different tables.

I created a common dim, Date dim. Common dim for date diff. The date dim just for your ref.

I also populated dates from one table to another to get date diff.

https://www.dropbox.com/s/y47ah38sr157l7t/Oder_delivery.pbix?dl=0

Let me know if this can solve your purpose.