Forum Discussion
Date Difference between 2 different tables
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 LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
You can try this calculate column :
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 |