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

     

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

      Scenario 1:

      Table 1:

      ID

      F_Nol_ dt

      Recovery_dt

      1

      11-Feb-2020

      11-Feb-2020

       

      Table 2:

      ID

      Closing_dt

      1

      14-Feb-2020

      In above scenario, if I calculate the date difference using the Dax

      Date_diff = DATEDIFF(Table1[Recovery_dt],MAXX(RELATEDTABLE(Table2),Table2[Closing_dt]),DAY)

      I am getting a correct value of date difference in a many to many relationship.

      Output:

      ID

      F_Nol_ dt

      Closing_dt

      Date_diff

      1

      11-Feb-2020

      11-Feb-2020

      3

       

      Scenario 2:

      Table 3:

      ID

      F_Nol_ dt

      Recovery_dt

      1

      11-Feb-2020

      11-Feb-2020

      1

      11-Feb-2020

      12-Feb_2020

       

      Table 4:

      ID

      Closing_dt

      1

      14-Feb-2020

      2

      19-Feb-2020

       

      In above scenario, if I calculate the date difference using the Dax

      Date_diff = DATEDIFF(Table3[Recovery_dt],MAXX(RELATEDTABLE(Table4),Table4[Closing_dt]),DAY)

      I am not getting a correct value of date difference in a many to many relationship.

      Output:

      ID

      F_Nol_ dt

      Closing_dt

      Date_diff

      1

      11-Feb-2020

      11-Feb-2020

      6

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Wow, thank you so much. It works

    • rnimmala's avatar
      rnimmala
      Frequent Visitor
      2 years ago

      Excellent solution.  Thank you. !!!