Measure or column to calculate number of changes

[ArashZ]

Hi,

I Have a table of users with their status, involving either active or inactive.

Date Status StatusText User
2021-01-07 16:32 0 Activation A
2021-03-08 16:32 1 Deactivation A
2021-01-06 16:32 0 Activation B
2021-03-05 16:32 1 Deactivation B
2021-03-03 16:32 1 Deactivation H
2021-02-18 16:32 1 Deactivation G
2021-02-09 16:32 1 Deactivation J
2021-01-05 16:32 0 Activation V
2021-02-03 16:32 1 Deactivation E
2021-02-04 16:32 0 Activation E
2021-01-03 16:32 0 Activation P
2021-01-04 16:32 1 Deactivation P
2021-01-16 16:32 1 Deactivation L
2021-01-18 16:32 0 Activation L
2021-01-10 16:32 1 Deactivation T
2021-01-15 16:32 0 Activation T
2021-01-01 16:32 0 Activation Q
2021-01-02 16:32 1 Deactivation Q

I want to create a graph that shows the number of users who have changed from active to inactive or vice versa. (The graph should not represent those who have just one status, such as H, G, J, etc). In fact, date on-axis and the number of changes in value. The date in the graph should reflect the change date (deactivation date) not the activation date. For example, in March, we have two changes (User A and B), and the graph should represent that.

[v-easonf-msft · Accepted solution]

It is recommended to create a calendar table first.

Create a calculated column  as beow:

RecordID by each user = 
RANKX (
    FILTER ( 'Table', 'Table'[User] = EARLIER ( 'Table'[User] ) ),
    'Table'[Date],
    ,
    ASC,
    DENSE
)

Then count  the number of users who have changed :
Measure:

Number of record =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[User] ),
    FILTER ( 'Table', 'Table'[RecordID by each user] >= 2 )
)

Please check my attached sample file for more detail.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ArashZ]

@v-easonf-msftHi

Thanks for providing the solution. However, I am looking for separating the graphs that one shows "active to deactive" and the other "deactive to active". Is it possible to help?

In the graph that you provided we can see the aggregated data.