Logic Required for solving a date calculation problem

[DJKarma2]

UnitReg IDDate

1	123	1 jan 2023
1	123234	2 jan 2023
1	2345345	3 jan 2023
1	34545364	4 jan 2023
1	655345	13 jan 2023
1	4562343	15 jan 2023
1	5345345	17 jan 2023
1	343	29 jan 2023
1	45634	8 feb 2023
1	63463	9 feb 2023
2	45345	1 jan 2023
2	36456	2 jan 2023
2	234	3 jan 2023
2	6534542	4 jan 2023
2	53463	13 jan 2023
2	46235	15 jan 2023
3	2353	17 jan 2023
3	63	29 jan 2023
3	5252235	8 feb 2023
3	3525	9 feb 2023

I have this table, I want to create a calculated column, that flags 1 if any date per id for any employee falls in 7 days later or after.
The output looks like this, if you notice, it should flag the sets of dates if they lie between 7 days of each other. i.e it should also handle sets.

Furthermore, it should work for individual ids, and not consider datediff between different IDs so I guess you can ignore the reg id column.

UnitReg IDDateFlag

1	123	1 jan 2023	1
1	123234	2 jan 2023	1
1	2345345	3 jan 2023	1
1	34545364	4 jan 2023	1
1	655345	13 jan 2023	1
1	4562343	15 jan 2023	1
1	5345345	17 jan 2023	1
1	343	29 jan 2023	0
1	45634	8 feb 2023	1
1	63463	9 feb 2023	1
2	45345	1 jan 2023	1
2	36456	2 jan 2023	1
2	234	3 jan 2023	1
2	6534542	4 jan 2023	1
2	53463	13 jan 2023	1
2	46235	15 jan 2023	1
3	2353	17 jan 2023	1
3	63	29 jan 2023	0
3	5252235	8 feb 2023	1
3	3525	9 feb 2023	1

[v-rongtiep-msft]

Hi @DJKarma2 ,

I'm very sorry I have some questions. "flags 1 if any date per id for any employee falls in 7 days later or after." and "you can ignore the reg id column." According to the Unit column, Shouldn't it be these columns that output 1?

1	5345345	2023/1/17
1	343	2023/1/29
1	45634	2023/2/8
1	63463	2023/2/9

And why is the output of the next two lines 0?

1

343

2023/1/29

0

3

63

2023/1/29

0

How to Get Your Question Answered Quickly 

 please provide more details with your desired output (or some sample data) .

Best Regards
Community Support Team _ Polly

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