Forum Discussion
Compare dates within same department, same ID, different status
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.
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.
4 Replies
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.Thank you for making it in a way I can understand and learm from it.
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.
