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 Key Mark date Present Days Group 1 9/3/2024 2 1 9/17...
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
            )
        )
)

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,