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,
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?
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
Wow, thank you so much. It works
Excellent solution. Thank you. !!!