Changing status among users

[ArashZ]

Hi,

I have a table like below:

Date	Status text	Status	User
2021-01-02	Deactive	1	A
2021-01-24	Active	0	A
2021-02-04	Active	0	B
2021-03-10	Deactive	1	B
2021-03-12	Active	0	C
2021-03-15	Deactive	1	C
2021-04-19	Active	0	C
2021-05-10	Deactive	1	D
2021-05-11	Active	0	D

I am looking to create two separate column charts. One to see the trend of the number of users whose status changed from "active to Deactive" and the other "Deactive to active". For example, when it comes to "deactive to active" chart, the total number is 3 (Users A, C, and D) and for the "Active to deactive" chart, the number is 2 (Users B and C) like below: As you can see the latest date is important. In fact, for user C, it first changed from active to deactive in March, and then in April, it became active again.

Also, that would be ideal by clicking the graph I can see the instances in the table.

[Anonymous · Accepted solution]

DEFINE 

MEASURE Data[# Deactive->Active] = 
// Number of users that have at least one status
// of Active in the current period and the previous
// status (taking into account ALL the time before
// this status) was Deactive.
// For a measure that will do the oposite, from
// active to deactive, just swich the values
// of the two variables below.
var FromState = 1 -- "Deactive"
var ToState = 0 -- "Active"
return
SUMX(
    DISTINCT( Data[User] ),
    // This formula returns 1 if a suitable
    // change of state exists and BLANK if
    // not.
    CALCULATE(
        var DateTimesOfInterest =
            FILTER(
                SUMMARIZE(
                    Data,
                    Data[DateTime],
                    Data[Status]
                ),
                Data[Status] = ToState
            )
        // This variable returns the number of 
        // DateTimes from the above table which
        // have a predecessor (record) where
        // the status equals Deactive.
        var SuitableDateTimeCount =
            COUNTX(
                DateTimesOfInterest,
                var CurrentDateTime = Data[DateTime]
                return
                    // This count can only be either 1 or BLANK.
                    // If BLANK, COUNTX will return BLANK.
                    COUNTROWS(
                        FILTER(
                            TOPN(1,
                                CALCULATETABLE(
                                    Data,
                                    Data[DateTime] < CurrentDateTime,
                                    ALLEXCEPT( Data, Data[User] )
                                ),
                                Data[DateTime],
                                DESC
                            ),
                            Data[Status] = FromState
                        )
                    )
            )
        var ChangeOfStateExists =
            SuitableDateTimeCount > 0
        return
            if( ChangeOfStateExists, 1 )
    )
)

MEASURE Data[# Active->Deactive] = 
var FromState = 0 -- "Active"
var ToState = 1 -- "Deactive"
return
SUMX(
    DISTINCT( Data[User] ),
    CALCULATE(
        var DateTimesOfInterest =
            FILTER(
                SUMMARIZE(
                    Data,
                    Data[DateTime],
                    Data[Status]
                ),
                Data[Status] = ToState
            )
        var SuitableDateTimeCount =
            COUNTX(
                DateTimesOfInterest,
                var CurrentDateTime = Data[DateTime]
                return
                    COUNTROWS(
                        FILTER(
                            TOPN(1,
                                CALCULATETABLE(
                                    Data,
                                    Data[DateTime] < CurrentDateTime,
                                    ALLEXCEPT( Data, Data[User] )
                                ),
                                Data[DateTime],
                                DESC
                            ),
                            Data[Status] = FromState
                        )
                    )
            )
        var ChangeOfStateExists = SuitableDateTimeCount > 0
        return
            if( ChangeOfStateExists, 1 )
    )
)

[ArashZ]

@Anonymous @Jihwan_Kim @PaulOlding Thanks guys.

[daxer-almighty]

What if a user goes within the same day through multiple changes? How will then one know from which change to which change they went? Is the property that each user can have at most 1 status on a single day invariant here?

[ArashZ]

@daxer-almighty 

It's a rare situation that status changes more than once a day, but ideally, the formula should capture that.

To be more precise, the date captures the exact time like below:

2021-05-23 20:00

2021-06-15 15:31