Status Tracking

[tb92]

Hello everyone, I have the following table;

ID               Date          Status

1	05/05/2023	A
1	06/05/2023	B
2	01/02/2023	A
2	01/04/2023	C
3	05/05/2023	A
3	06/05/2023	C

I would like to define the following ;

Measure 1 = COUNT (Status A to Status B) = 1 Total

Measure 2 = COUNT (Status A to Status C) = 2 Total 

Which would allow to me to count how many unique IDs have changed from A to B and from A to C over time.

Could anyone provide me with some help on this?

Many Thanks 

[Ritaf1983 · Accepted solution]

Hi @tb92 
Dax formula for your first request :

Distinct Count A To B =

CALCULATE(

    DISTINCTCOUNT('Table'[ID]),

    FILTER(

        'Table',

        'Table'[Status] = "B" &&

        CALCULATE(

            MAX('Table'[Date]),

            FILTER(

                'Table',

                'Table'[ID] = EARLIER('Table'[ID]) &&

                'Table'[Status] = "A"

            )

        ) <'Table'[Date]

    )

)

For Second :

Distinct Count A To C =

CALCULATE(

    DISTINCTCOUNT('Table'[ID]),

    FILTER(

        'Table',

        'Table'[Status] = "C" &&

        CALCULATE(

            MAX('Table'[Date]),

            FILTER(

                'Table',

                'Table'[ID] = EARLIER('Table'[ID]) &&

                'Table'[Status] = "A"

            )

        ) <'Table'[Date]

    )

)

Link to sample file 

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

[tb92]

Thank you this is perfect 🙂 

[Ritaf1983]

It was my pleasure to assist 🙂