Checking for change in text category between date and last date for a reference

[julesdude]

Hi all,

A simple problem which I haven't quite been able to find the solution for in previous posts, but have found close.

Have the folloiwng table:

Reference	Date	Value Method
a	20/08/2022	method a
b	20/03/2022	method a
a	24/04/2022	method a
c	01/07/2021	method b
b	01/02/2022	method a
a	02/02/2022	method b
c	01/05/2021	method c

I need to be able to provide a Y or an N for each row here where the Value Method has changed since last time based on the date and Reference. I think a calculated column is what I had in mind for this where it checks the previous most recent date for that reference and sees whether there is a difference in the Value Method, placing Y if there is or N if there isn't. 

How can I achieve this?

[v-yanjiang-msft · Accepted solution]

Hi @julesdude ,

According to your description, here's my solution, create a calculated column.

Column =
VAR _Date =
    MAXX (
        FILTER (
            'Table',
            'Table'[Reference] = EARLIER ( 'Table'[Reference] )
                && 'Table'[Date] < EARLIER ( 'Table'[Date] )
        ),
        'Table'[Date]
    )
VAR _lastM =
    MAXX (
        FILTER (
            'Table',
            'Table'[Reference] = EARLIER ( 'Table'[Reference] )
                && 'Table'[Date] = _Date
        ),
        'Table'[Value Method]
    )
RETURN
    IF ( _lastM = BLANK (), "N", IF ( 'Table'[Value Method] = _lastM, "N", "Y" ) )

Get the correct result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[julesdude]

Hi @v-yanjiang-msft 

Thank you very much, it works very well indeed.

[amitchandak]

@julesdude , Create a new column  like 

new colu=

var _max = maxx(filter(Table, [Reference] = earlier([Reference])  && [Date] < earlier([Date]) ) , [Date])

return

if( [Method] <> maxx(filter(Table, [Reference] = earlier([Reference])  && [Date] =_max ) , [Method])  , "N", "Y")

[julesdude]

Been trying to adapt this, it is still not working. 

The table with the new column becomes as follows:

If we look at the first two rows - both have the same reference - 'a'. The top row is saying 'Y' - that yes the Value Method name has changed since the previous date. This should be N because it hasn't changed. 

Reference b is also incorrect - both rows should have N as the Value method has not changed.

And c is also incorrect - the two rows with c are different, so the first row dated 01 July 2021 should be Y as the Value Method has changed since the previous date.

Grateful for any help to amend this logic please.

[julesdude]

Hi @amitchandak thank you for helping again!

Applying the solution it is not giving me the results I was expecting. 

I'll exaplin again the logic.

Basically for each row, the previous date for the reference should be found and then if the Value Method is different then it should return 'Y' in the column. If it is not different for the previous date for the reference, or if there is no previous date that can be found on record, then it should return 'N'.