Change in Category - First Category and date and all following dates when category changes

[TiffanyT_IL]

Greetings!
I am looking for a solution to display the first date and first category and then all subsequent changes in a category and its associated date. I found an earlier post that helped immensely, but I have been unable to modify it to solve my specific problem. Thoughts?

Here is a moc data set:

ID         Date                Category

A100	6/10/2021	RN
A100	6/11/2021	RN
A100	6/12/2021	RN
A100	6/18/2021	RN
A100	6/19/2021	LPN
A100	6/19/2021	LPN to RN ADJ
A100	6/21/2021	LPN
A100	6/21/2021	LPN to RN ADJ
A100	6/22/2021	LPN
A100	6/22/2021	LPN to RN ADJ
A100	6/22/2021	LPN to RN ADJ
A100	7/3/2021	RN
A100	7/5/2021	RN
A100	7/6/2021	RN
A100	7/7/2021	RN

Desired Report

ID	Date of Change	Category
A100	6/10/2021	RN
	6/19/2021	LPN
	6/19/2021	LPN to RN ADJ
	6/21/2021	LPN
	6/21/2021	LPN to RN ADJ
	6/22/2021	LPN
	6/22/2021	LPN to RN ADJ
	7/3/2021	RN

Calculated Column Category Change Date =

VAR _Status = 'Table1'[Category]

VAR _Date = Table1[Date]

VAR _FIRSTClassDt =

CALCULATE (

MIN ( Table1[Date] ),

ALLEXCEPT ( Table1, Table1[ID] ),

Table1[Category] <> _Status,

Table1[Date] > _Date

)

RETURN _FIRSTClassDt

Calculated Column Category Change =

VAR _LastChangeDate = Table1[Category Change Date]

VAR _Class = Table1[Category]

RETURN

CALCULATE(

MIN (Table1[Category] ),

ALLEXCEPT (Table1,Table1[ID]),

Table1[Category] <> _Class

)
Which returned this:

If possible, I would prefer to have a measure to do this.

Any help would be greatly appreciated!

[TiffanyT_IL]

This is great! Thank you so much.

My actual data was a bit more complex than the sample but with grouping, adding the index to the grouping and an extra condition in the filter to account for more IDs, it was perfect.

Thank you again!

[v-rongtiep-msft · Accepted solution]

Hi @TiffanyT_IL ,

I have created a simple sample, please refer to it to see if it helps you.

Add an index column first.

Then create a measure.

Measure =
VAR _1 =
    CALCULATE (
        MAX ( 'Table'[category] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Index]
                = SELECTEDVALUE ( 'Table'[Index] ) - 1
        )
    )
RETURN
    IF ( MAX ( 'Table'[category] ) <> _1, MAX ( 'Table'[Date] ), BLANK () )

Or a column.

Column = 
VAR _1 =
    CALCULATE (
        MAX ( 'Table'[category] ),
        FILTER (
             ( 'Table' ),
            'Table'[Index]
                = EARLIER(  'Table'[Index] ) - 1
        )
    )
RETURN
    IF ( ( 'Table'[category] ) <> _1, ( 'Table'[Date] ), BLANK () )

Finally filter the measure or column is not blank.

Best Regards

Community Support Team _ Polly

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