Forum Discussion
Comparing different times Data in same table
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.
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
2 Replies
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
Thanks for the help Pat
