Average of DateDIFF in Power BI with dates in two different tables

[dgkallan]

I'm working with employee data.  I have one table that is requisitions and one that is employees.  I am trying to calculate the tenure.

The rough layout of requisitions is

ReqID

EmpID

HireDt

The rough layout of employees is

EmpID

TermDt

If the TermDt is blank, I need to assume today's date.  I did that with a conditional column in PowerBI.

I was able to calculate the diffference between the two by using a measure:

Tenure = (DATEDIFF(MAX(Requisitions[Hire Dt]),MAX(Employees[TermCurr Date]),DAY))

But, I can't average the measure.  Any suggestions?

[edhans]

So, do you have an answer, or still need help. If you have an answer I'd apprecate it if my response was marked as such, even though I had to guess at the shape of the model. It will also allow people to see this is solved.

If you need additional help, post back with exactly what the issue is.

[dgkallan]

Thank you so much for the help.  I was able to solve the last piece by moving the relative to the field not in the Employees data

[edhans · Accepted solution]

You don't need DATEDIFF and you don't need a calculated column. You didn't tell us how the tables are related. There should be a DIM table or something relating them. 

Tenure =
AVERAGEX (
    Employees,
    RELATED ( Requisitions[HireDate] )
        - IF ( ISBLANK ( Employees[TermDT] ), TODAY (), Employees[TermDT] )
)

This will iterate over the emplyee table and average the diff between the hire date and the termdt or TODAY(). But without an understanding of how your model is set up, that RELATED() may not work.

[dgkallan]

The tables are joined on EmpID.  

This worked, except it would Term Dt (or today's date) - Hire Date.  When I tried switching it didn't work

Tenure 2 = AVERAGEX(Employees,RELATED(Requisitions[Hire Dt])-IF(ISBLANK(Employees[Term Date]),TODAY(),Employees[Term Date]))

Switched to:

Tenure 2 = AVERAGEX(Employees,

RELATED(IF(ISBLANK(Employees[Term Date]),TODAY(),Employees[Term Date])

-Requisitions[Hire Dt])

)