please help me with below scenario, need to compare ID's with previous date

[aditya369_]

Hi Team,

Good evening. Need help to get the expected output.i am attaching excel file, in that having two columns

1. Date and Invoice ID

2. I have given expected output column

Note: we need to compare invoice ID's with previous date, if it is not matching then "New" else if matches then "OLD". i tried different ways but unable get that.please help me.

please find the attached image for your reference.

[v-binbinyu-msft · Accepted solution]

Hi @aditya369_ ,

Please try below steps:

1. add a index column in Power Query Pane

2. create a measure with below dax formula

Measure =
VAR _idx =
    SELECTEDVALUE ( 'Table'[Index] )
VAR _id =
    SELECTEDVALUE ( 'Table'[Invoice ID] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [Index] < _idx )
VAR _str =
    CONCATENATEX ( tmp, [Invoice ID], "," )
VAR _val =
    CONTAINSSTRING ( _str, _id )
RETURN
    IF ( _val, "Old", "New" )

3. add a table visual with fileds and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.