Mark if status changes per id and date

[Anna_maija]

Hello!

I would like to create calculated column (to use as a slicer) when status change by using dax. Here is the example of data.

[v-yilong-msft · Accepted solution]

Hi @Anna_maija ,
Based on your requirement, here is my answer.

I create a table as you mentioned.

Then I go to the Power Query to insert an Index column.

Then I create an calculated column, here is the DAX code.

StatusChangeFlag =
VAR _PreviousIndex =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] < EARLIER ( 'Table'[Index] ) )
    )
VAR _PreviousStatus =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[Status] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = _PreviousIndex )
    )
RETURN
    IF ( _PreviousStatus <> BLANK () && 'Table'[Status] <> _PreviousStatus, 1 )

Finally, when we change the letter, it will give us a "1".

Best Regards

Yilong Zhou

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