Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Date Difference between 2 different tables

I have 2 date fields from 2 different tables. I want to find the date difference between the date fields. So, I created a measure as follows:

 

Measure = DATEDIFF ( SELECTEDVALUE ( 'Table 1'[Start_date] ), SELECTEDVALUE ( 'Table 2'[End_date] ), DAY )

 

This is not working for the huge amount of data, its only loading . If I want to create a column, what is the dax for it. What is the solution for this?

Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi Anonymous ,

    You can try this calculate column :

     

    Column = DATEDIFF(MAXX(RELATEDTABLE(Table4),'Table4'[Start Date]),'Table5'[End Date],DAY)
     
    Here is the demo, please try it:(refer table4 and table5)
     
    Best Regards,
    Yingjie Li
     
    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

     

8 Replies

  • v-yingjl's avatar
    v-yingjl
    Community Support
    6 years ago

    Hi Anonymous ,

    First you should create relationship between two tables:

    1:1 relationship:

     

    For 1:1 relationship you can use this calculate column in 1 site:

    Column = DATEDIFF(Table1[Start Date],RELATED(Table2[End Date]),DAY)

     

    1:* relationship:

     

    For 1:* relationship, you can use this calculate column in 1 site:

    Column = DATEDIFF(MAXX(RELATEDTABLE(Table3),'Table3'[Start Date]),Table2[End Date],DAY)

     

    Or you can use another calculate column in * site:

    Column = DATEDIFF(Table3[Start Date],RELATED(Table2[End Date]),DAY)

     

    Here is the demo , please try it:

    PBIX 

     

    1:1 table relationship, please refer table1 and table2, 1:* table relationship, please refer table2 and table3

     

    Best Regards,

    Yingjie Li

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

       

      In my scenario, the relationship between the dimension table and fact table is 'many-to-many' . Could I know what is the solution for it?

      • v-yingjl's avatar
        v-yingjl
        Community Support
        6 years ago

        Hi Anonymous ,

        You can try this calculate column :

         

        Column = DATEDIFF(MAXX(RELATEDTABLE(Table4),'Table4'[Start Date]),'Table5'[End Date],DAY)
         
        Here is the demo, please try it:(refer table4 and table5)
         
        Best Regards,
        Yingjie Li
         
        If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Can you explain a little better how the data model is structured? Are the two tables linked through a relationship? Are they dimensions to another table?

    If they are linked through a relationship you can create a column like: 

    Datediff(Table1[Start_date],RELATED(Table2[End_date]),DAY)


    Then SUM the result column in a measure and display it in a chart

    If they are dimensions to another table you have to use a related also on the first table to retrieve the value from both tables.