Tracking Changes under Unique ID

[graefs]

Data Example: Table1

Part #	Type	Date
123	A	1/1/24
123	B	1/3/24
123	A	1/6/24
456	B	1/1/24
456	B	1/2/24
456	A	1/9/24

Desired Result: Table2

Part #	Variance
123	2
456	1

[Anonymous · Accepted solution]

Hi @graefs ,

I’ve made a test for your reference:

1\I assume there is a table(Table1)

2\Add a new column for Table1

TypeChange =

VAR CurrentType = 'Table1'[Type]

VAR PreviousType =

    CALCULATE(

        MAX('Table1'[Type]),

        FILTER(

            'Table1',

            'Table1'[Part] = EARLIER('Table1'[Part]) &&

            'Table1'[Date] < EARLIER('Table1'[Date])

        )

    )

RETURN IF(CurrentType <> PreviousType && NOT(ISBLANK(PreviousType)), 1, 0)

3\Create a new calculate table

NewTable = SUMMARIZE(Table1,Table1[Part],"Variance",SUM(Table1[TypeChange]))

Best Regards,

Bof