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 Orders table the relationship is with the Due Date.  

 

I need find the difference between these two dates.   

 

Need Help

4 Replies

  • 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.

         

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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,