Forum Discussion

graefs's avatar
graefs
Frequent Visitor
1 year ago
Solved

Tracking Changes under Unique ID

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 # Var...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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