Group Rows by Relative Dates

[Anonymous]

Hello everyone,

I have a data table which looks like this:

Branch	WO#	Hours Worked	First Labor	Last Labor	Machine ID	Index
10	N85738	3.13	4/22/2019	4/22/2019	063280	15
10	N85840	0	4/24/2019		063280	14
10	N85839	4.78	4/24/2019	4/26/2019	063280	16
50	K32828	2	7/2/2019	7/3/2019	063280	17
50	K33128	8.5	8/14/2019	8/16/2019	063280	18
50	K33312	3.5	9/10/2019	9/10/2019	063280	19
70	Z17253	14.08	11/4/2019	11/15/2019	063280	20
70	Z17734	10.29	1/29/2020	2/5/2020	063280	21
70	Z17876	10.45	2/20/2020	3/10/2020	063280	22
70	Z17953	0	3/3/2020		063280	13
70	Z18103	0.55	3/30/2020	6/5/2020	063280	25
70	Z18193	0	5/5/2020		063280	9
70	Z18431	6.89	5/18/2020	6/1/2020	063280	24
70	Z18487	0	5/29/2020	5/29/2020	063280	23
70	Z18850	7.12	7/6/2020	7/17/2020	063280	26
70	Z19678	8.63	10/20/2020	11/18/2020	063280	27
70	Z20271	5.49	1/22/2021	2/1/2021	063280	28
70	Z20404	4.22	2/10/2021	2/18/2021	063280	29
60	M28513	12.47	2/17/2021	2/22/2021	063280	30
60	M28591	0	3/1/2021		063280	4

I need to group rows together and assign the resulting groups a Unique ID (per group) via a calculated column.

The grouping criteria: Where Machine ID is the same between rows, where the Date Difference between “Last Labor” and “First Labor” (between rows) is never more than 30 days +/- and exclude rows where “First Labor” and/or “Last Labor” are BLANK.

The desired results would look like this:

Branch	WO#	Hours Worked	First Labor	Last Labor	Machine ID	Index	Group Index
10	N85738	3.13	4/22/2019	4/22/2019	063280	15	1516
10	N85840	0	4/24/2019		063280	14
10	N85839	4.78	4/24/2019	4/26/2019	063280	16	1516
50	K32828	2	7/2/2019	7/3/2019	063280	17	171819
50	K33128	8.5	8/14/2019	8/16/2019	063280	18	171819
50	K33312	3.5	9/10/2019	9/10/2019	063280	19	171819
70	Z17253	14.08	11/4/2019	11/15/2019	063280	20	20
70	Z17734	10.29	1/29/2020	2/5/2020	063280	21	2122
70	Z17876	10.45	2/20/2020	3/10/2020	063280	22	2122
70	Z17953	0	3/3/2020		063280	13
70	Z18103	0.55	3/30/2020	6/5/2020	063280	25	232425
70	Z18193	0	5/5/2020		063280	9
70	Z18431	6.89	5/18/2020	6/1/2020	063280	24	232425
70	Z18487	0	5/29/2020	5/29/2020	063280	23	232425
70	Z18850	7.12	7/6/2020	7/17/2020	063280	26	26
70	Z19678	8.63	10/20/2020	11/18/2020	063280	27	27
70	Z20271	5.49	1/22/2021	2/1/2021	063280	28	282930
70	Z20404	4.22	2/10/2021	2/18/2021	063280	29	282930
60	M28513	12.47	2/17/2021	2/22/2021	063280	30	282930
60	M28591	0	3/1/2021		063280	4

I have tried everything I know to accomplish this without success and have been unable to find a solution online. Any help the community can provide would be extremely welcome!

[v-yanjiang-msft]

Hi @Anonymous ,

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ kalyj

[Anonymous]

Hello @v-yanjiang-msft ,

My problem was not resolved and I've closed the project as "not possible"; though I do greatly appreciate you trying to help!

[v-yanjiang-msft]

Hi @Anonymous ,

According to your description, here's my solution.

Create four calculated columns.

Column =
IF (
    'Table'[Last Labor] = BLANK ()
        || MAXX (
            FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) + 1 ),
            'Table'[First Labor]
        )
            = BLANK (),
    BLANK (),
    IF (
        DATEDIFF (
            'Table'[Last Labor],
            MAXX (
                FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) + 1 ),
                'Table'[First Labor]
            ),
            DAY
        ) <= 30,
        1
    )
)

Column 2 =
IF (
    'Table'[Column] <> 1,
    BLANK (),
    SUMX (
        FILTER (
            'Table',
            'Table'[Index] <= EARLIER ( 'Table'[Index] )
                && MAXX (
                    FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 ),
                    'Table'[Column]
                ) <> 1
        ),
        'Table'[Column]
    )
)

Column 3 =
IF (
    [Column 2] <> BLANK (),
    [Column 2],
    IF (
        MAXX (
            FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 ),
            'Table'[Column 2]
        )
            <> BLANK (),
        MAXX (
            FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 ),
            'Table'[Column 2]
        )
    )
)

Group Index =
IF (
    [Column 3] <> BLANK (),
    CONCATENATEX (
        FILTER ( 'Table', 'Table'[Column 3] = EARLIER ( 'Table'[Column 3] ) ),
        'Table'[Index]
    ),
    IF ( 'Table'[Last Labor] <> BLANK (), CONVERT ( 'Table'[Index], STRING ) )
)

 Get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

Hi @v-yanjiang-msft ! Thank you so much for your response. I implemented the additional columns that you outlined above but I ran into a problem on column 2. When I add it to the table, loading the column takes approximately 4 hours... "Working on it". The dataset I'm applying it to is about 350,000 rows. Any ideas how the calculation time could be reduced?

[v-yanjiang-msft]

Hi @Anonymous ,

Actually for column2, does not use too complex operations, mainly using a sumx function, it's even simpler than other formulas. As your sample data is too large, it does run slow. But if other formula work normally but only column2 takes approximately 4 hours, I doubt it's a coincidence, the computer freeze etc.

Best Regards,
Community Support Team _ kalyj

[Anonymous]

@v-yanjiang-msft  It is a simple operation, which is why I don't understand the slowness. Unfortunatelt, I can't reduce the size of my data.