Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Difference between two dates - from two different tables

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    amitchandak
    Super User
    6 years ago

    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.