Create a measure from two tables that are not connected or linked to each other

[Anonymous]

Hi,

I have two tables one is for Hiring Table and one is for Termination Table, both have Employee ID and what I need is to do the below:

Termination Table:

- Employee ID

- Term Date

- Term Category (voluntary , involuntary, Nutrue)

Hiring Table:

- Employee ID

- Hiring Date

What I need to do is to create a measure that can calculate how many employees were terminated before 6 months. The challenge is I cannot measure this using only Termination table as I don't have the Hiring Date, but I have it in the Hiring Table, is there any solution for this?

Thank you!

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Try this:

Hiring Date = 
MAXX(FILTER(ALL('Hiring Table'),'Hiring Table'[Employee ID]=MAX('Termination Table'[Employee ID])),[Hiring Date])

Datediff = 
DATEDIFF([Hiring Date],SELECTEDVALUE('Termination Table'[Term Date]),MONTH)

is6 = 
SUMX(ADDCOLUMNS('Termination Table',"_is6",IF([Datediff]<=6,1,0)),[_is6])

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

Num terminated 6 months =
var summaryTable = ADDCOLUMNS( SUMMARIZE( 'Terminated', 'Terminated'[Employee ID], 'Terminated[Term Date]),
    "@months worked", 
    var hireDate = LOOKUPVALUE( 'Hired'[Hire date], 'Hired'[Employee ID], SELECTEDVALUE( 'Terminated'[Employee ID] ) )
    return DATEDIFF( hireDate, SELECTEDVALUE( 'Terminated'[Term Date] ), MONTH )
)
return COUNTROWS( FILTER( summaryTable, [@months worked] < 6 ) )

[Anonymous]

Hi, thank you for your help. I tried your measure and the results came out inclduing all the total terminations (including more than 6 months hires), any suggestion to fix that?

[johnt75]

I don't immediately see the problem. Can you share some sample data or a pbix file ?