Average period between two dates in different tables

Question

I have two tables table 1: specificationId - startDate table 2: specificationId - endDate

Tables are connected by specificationId in pbi model.

How can I calculate average period between dates in measure?

I can easily do it when both dates are in the same table. I use CALCULATE (AVERAGEX ('table', DATEDIFF ( 'table'[startDate], 'table'[endDate]), DAY ))

But it doesn't work when dates are in different tables. What do I miss?

Answer 1

I recommend a relationship on the IDs between the two tables.

Then on the Table_start Table, create a calculated column:

date diff = 
VAR startDate = Table_start[startDate]
VAR endDate = RELATED(Table_end[endDate])

RETURN DATEDIFF(startDate, endDate, DAY)

Create a measure to get the average:

average days = AVERAGE(Table_start[date diff])

Result: