Compare dates within same department, same ID, different status

Hi bottos 

Please try this:

First of all, I create 2 table with dax:

Table 2 =
CALCULATETABLE (
    SELECTCOLUMNS (
        'Table',
        "Department1", 'Table'[Department],
        "Employee1", 'Table'[Employee ID],
        "Pre-StartDate", 'Table'[Start Date]
    ),
    'Table'[Status] = "Pre"
)

Table 3 =
CALCULATETABLE (
    SELECTCOLUMNS (
        'Table',
        "Department2", 'Table'[Department],
        "Employee2", 'Table'[Employee ID],
        "In-endDate", 'Table'[End Date]
    ),
    'Table'[Status] = "In"
)

Then create a new table:

Table 4 = CROSSJOIN('Table 2','Table 3')

Then add a calculate column:

diff =
IF (
    'Table 4'[Department2] = 'Table 4'[Department1],
    DATEDIFF ( 'Table 4'[In-endDate], 'Table 4'[Pre-StartDate], DAY )
)

The result is as follow:

The measure:

count =
CALCULATE (
    COUNTROWS ( 'Table 4' ),
    FILTER ( ALLSELECTED ( 'Table 4' ), 'Table 4'[diff] > 30 )
)

The result of the sample data is zero.

Best Regards

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