Find if the data was converted in data with past date

[g_js]

Hi,
I am tring to get to a solution where i am able to know if the data was converted from A to C 

Date               Column 1               Column 2
1/1/2021           123                             A

2/1/2021           123                             B
3/1/2021           123                             C
4/1/2021            123                            D
12/1/2021          456                            A
12/2/2021          456                            C

1/1/2021            789                            E

2/1/2021            789                            B
3/1/2021            789                            C
4/1/2021            789                            D


I ind to out for a particular value of column 1 Column2 value is C but was A in the past. so basically 3 and 6 line will be tue
May be create a new column with that formulas.

Thanks and Regards,
Gagan

[v-yangliu-msft · Accepted solution]

Hi  @g_js ,

Here are the steps you can follow：

1. Create calculated column.

rank =
RANKX(FILTER(ALL('Table'),[Column1]=EARLIER([Column1])),[Date],,ASC,Dense)

New Column =
IF(
    [Column2]="A",
    IF(
        "C" in SELECTCOLUMNS(FILTER('Table',[rank]>EARLIER([rank])&&[Column1]=EARLIER([Column1])),"1",[Column2]),
        "C",
        [Column2]),
        [Column2])

2. Result:

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@g_js , A new column will give true or false

new column =
var _1 = countx(filter(Table, [Column 1] =earlier([Column 1]) && [Date] <earlier([Date]) && [Column 2] ="A"),[Column 1])
return
if([Column 2] = "C" && not(Isblank(_1)), true(), false())