Compare dates within same department, same ID, different status

[bottos]

I have searched for a similar issue, but I am struggling to find a code that I can adapt. So any help would be greatly appreciated.

I have the following table. This is just an extract, because there are multiple slot numbers within the same department, and multiple departments with their own slot numbers.

What is required is to identify  when "Pre" and "In" overlap for more than 30 days, and count how many times it will happen. I need to do this comparison within the same department and within the same slot number.

The data looks like this:

Department	Slot number	Employee ID	Status	Start Date	End Date
Sales	73223	1111-A	Pre	3/12/2024	8/26/2025
Sales	73223	1111-B	Pre	3/12/2024	8/26/2025
Sales	73223	1112-A	In	1/31/2023	7/16/2024
Sales	73223	1112-B	In	1/31/2023	7/16/2024
Sales	73223	1113-A	In	8/22/2023	2/7/2025
Sales	73223	1113-B	In	8/22/2023	2/7/2025
Sales	73223	1114-A	In	12/12/2023	5/27/2025
Sales	73223	1114-B	In	12/12/2023	5/27/2025
Sales	73223	1115-A	Pre	3/12/2024	8/28/2025
Sales	73223	1115-B	Pre	3/12/2024	8/28/2025
Sales	73223	1116-A	Pre	7/16/2024	1/1/2026
Sales	73223	1116-B	Pre	7/16/2024	1/1/2026

The comparison does not need to happen between "In"s, but only with "Pre"s with "In"s. Also "Pre" means people that will join (start and end date are alway future) and "In" have joined already (start date is always in the past, end date future).

Thank you.

[Anonymous · Accepted solution]

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.

[bottos]

Thank you for making it in a way I can understand and learm from it.

[danextian]

Hi @bottos 

Given you sample data, what is your expected result?  Which row is to be compared against which row? Count how many times it will happen based on what date - today, selected date?

[bottos]

Hi! I have to compare each row that has the status "Per" with every row that has the status "In"; for each comparison, I need to also compare if the Start Date field of the "Pre" row is 30 days more than the End Date of the "In" row. If it is, I need to flag it, and count it in the end.