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
            )
        )
)

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
            )
        )
)