Comparing different times Data in same table

[g_js]

Hi,
I was looking into a problem where i need to find the when one column in the data was changed in the table.


Example-
Date                           Column1                          column2               

1/1/2021                       123                                      A                      

2/2/2021                       123                                      A

3/2/2021                       123                                      B

4/2/2021                       123                                      B

1/2/2021                       456                                      A

2/2/2021                       456                                      B
3/2/2021                       456                                      B


So for the above example i am looking for a way to find the date the column2 data was changed.
Like create new column3 filled with 1 at  row 3 and 6

Thanks for the help.

[g_js]

Thanks for the help Pat

[mahoneypat · Accepted solution]

Here is a column expression that gets that result.  I called the table "Change" so replace that with your actual table name.

Change From Previous =
VAR vThisDate = Change[Date]
VAR vPrevDate =
    CALCULATE (
        MAX ( Change[Date] ),
        ALLEXCEPT (
            Change,
            Change[ Column1]
        ),
        Change[Date] < vThisDate
    )
VAR vCurrent = Change[Column2]
VAR vPrevValue =
    CALCULATE (
        MIN ( Change[Column2] ),
        ALLEXCEPT (
            Change,
            Change[ Column1]
        ),
        Change[Date] = vPrevDate
    )
RETURN
    IF (
        OR (
            ISBLANK ( vPrevDate ),
            vCurrent = vPrevValue
        ),
        0,
        1
    )

Pat