Forum Discussion

spb's avatar
spb
Frequent Visitor
4 years ago

Calculate days between 2 columns in different table

Looking to calculate the difference in the 2 dates.   Table1 'ID' 'Late Finish date' 1      10 May 22   Table 2 'ID' 'Early Finish Date' 1      20 May 22   Answer - 10 days. To be included...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

spb , if you have ID column, which is in common in both, then create a common ID table

 

ID= distinct(union(distinct(Table1[ID]),distinct(Table2[ID])))

 

Join with both tables on ID and create measure like

 

Sumx(Values(ID[ID]), calculate(datediff(min(Table1[Late Finish date]), Max(Table2[early Finish date]), Day) ))

spb's avatar
spb
Frequent Visitor
4 years ago

I was unaware of the 'Union' function. I note that the function mentions the 2 tables need to be the same structure ie have the same number of columns. The 2 tables have a common ID field in one of the columns, but have other different columns and number of columns in each table. 

 

Given this, presumably the solution could not apply in this case.

 

Look forward to understand a workaround.

Thanks