Forum Discussion

CEllis's avatar
CEllis
Resolver I
1 year ago
Solved

Adding MAX date before change in another column

 

Hi All,

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

 

Could anyone help please

 

Primary KeyMark datePresentDaysGroup
19/3/2024219/17/2024
19/4/2024219/17/2024
19/5/2024219/17/2024
19/6/2024219/17/2024
19/9/2024219/17/2024
19/10/2024219/17/2024
19/11/2024219/17/2024
19/12/2024219/17/2024
19/13/2024219/17/2024
19/16/2024219/17/2024
19/17/2024219/17/2024
19/18/20240  
19/19/20240  
19/20/20240  
19/23/2024219/23/2024
19/24/20240  
19/26/2024219/27/2024
19/27/2024219/27/2024
19/30/20240  
110/1/20242110/3/2024
110/2/20242110/3/2024
110/3/20242110/3/2024
110/4/20240  
110/7/20242110/7/2024
110/8/20240  
110/9/20242110/10/2024
110/10/20242110/10/2024

 

Many thanks

 

Need Help
  • ThxAlot's avatar
    ThxAlot
    1 year ago
    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
            )
        )
)

2 Replies

  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    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,

  • ThxAlot's avatar
    ThxAlot
    Super User
    1 year ago
    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
            )
        )
)