Adding MAX date before change in another column

[CEllis]

Hi All,

I am trying to add a table column to end up with the column called group below.

Could anyone help please

Primary Key	Mark date	Present	Days	Group
1	9/3/2024	2	1	9/17/2024
1	9/4/2024	2	1	9/17/2024
1	9/5/2024	2	1	9/17/2024
1	9/6/2024	2	1	9/17/2024
1	9/9/2024	2	1	9/17/2024
1	9/10/2024	2	1	9/17/2024
1	9/11/2024	2	1	9/17/2024
1	9/12/2024	2	1	9/17/2024
1	9/13/2024	2	1	9/17/2024
1	9/16/2024	2	1	9/17/2024
1	9/17/2024	2	1	9/17/2024
1	9/18/2024	0
1	9/19/2024	0
1	9/20/2024	0
1	9/23/2024	2	1	9/23/2024
1	9/24/2024	0
1	9/26/2024	2	1	9/27/2024
1	9/27/2024	2	1	9/27/2024
1	9/30/2024	0
1	10/1/2024	2	1	10/3/2024
1	10/2/2024	2	1	10/3/2024
1	10/3/2024	2	1	10/3/2024
1	10/4/2024	0
1	10/7/2024	2	1	10/7/2024
1	10/8/2024	0
1	10/9/2024	2	1	10/10/2024
1	10/10/2024	2	1	10/10/2024

Many thanks

[ThxAlot · Accepted solution]

Group = 
IF(
    DATA[Present] = 2,
    VAR __dt = DATA[Mark date]
    VAR __gap =
        CALCULATE(
            MIN( DATA[Mark date] ),
            ALLEXCEPT( DATA, DATA[Primary Key] ),
            DATA[Present] = 0,
            DATA[Mark date] > __dt
        )
    RETURN
        IF(
            ISBLANK( __gap ),
            CALCULATE( MAX( DATA[Mark date] ), ALLEXCEPT( DATA, DATA[Primary Key] ) ),
            CALCULATE(
                MAX( DATA[Mark date] ),
                ALLEXCEPT( DATA, DATA[Primary Key] ),
                DATA[Present] = 2,
                DATA[Mark date] < __gap
            )
        )
)

[DataNinja777]

Hi @CEllis ,

You can achieve this in Power Query by using the Fill Down feature. Here's how:

1. Load your table into Power Query.
2. Sort the Mark date column in ascending order.
3. Select the Group column (which will initially have null values for gaps).
4. Go to the Transform tab and click Fill Down to propagate the last valid date across rows until a new value is encountered.

This will fill the Group column with the reference dates for each continuous streak where Present = 2. 

Best regards,