Cummulative Values as of the End of the month - Values can Increase or Decrease as well

[jeebee]

I have a requirement where we need to plot the cummulative figures at a month level and the count at month level can increase/decrease because same users jump across statuses across time.

Sample Data:

User ID	Active/Inactive Flag	Last Modified Timestamp	Latest Record?
CP123	Active	20-Jan-21	No
CP123	Inactive	21-Feb-22	Yes
CP124	Active	20-Jan-21	No
CP124	Inactive	5-Apr-22	No
CP124	Active	7-Apr-22	Yes
CP125	Active	6-Mar-21	Yes
CP126	Active	20-Apr-21	Yes
CP127	Active	4-Jun-21	Yes
CP128	Active	19-Jul-21	Yes
CP129	Active	2-Sep-21	Yes
CP130	Active	17-Oct-21	No
CP130	Inactive	1-Dec-21	Yes
CP131	Inactive	20-Jan-21	Yes
CP132	Inactive	20-Jan-21	Yes
CP133	Inactive	20-Jan-21	Yes
CP134	Inactive	20-Jan-21	Yes
CP135	Inactive	20-Jan-21	Yes
CP136	Inactive	7-Apr-22	Yes
CP137	Inactive	8-Apr-22	Yes
CP138	Inactive	9-Apr-22	No
CP138	Active	9-Jun-22	Yes

The ask is -

Chart to represent Active/Inactive Users count over time (Cummulative)

ex: How many Active/Inactive Users were present as of Jan-2022,Feb-2022 and so on...

Expected Output:

	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22
Active Users Total - Cummulative	7	6	6	6	6	7
Inactive Users Total - Cummulative	6	7	7	10	10	9

Note: In above Output that values need to increase but it can also decrese when compared against the prevous month (Ex:  Active User count have reduced in Feb month compared to Jan month )

How to implement this?

[jeebee]

Thank you for the revert, but above is not what Im looking for. 

In the output that im expecting, the count can also "decrease" when compared with the earlier month, in above output shared the count only keeps on increasing month after month which is not what im looking for

Example:

Lets consider we  had 13 users (7 active and 6 inactive users) in total have  as of end January.

Among the 7 active users was User A who was having the status "Active" as of end January. Hence this was user was counted against Active users count for the month of January

The same User A, lets assume that his status changed to "Inactive" as of end February and in Feb month no new users got added to the system. Hence the final count expected is still 13 users (6 active and 7 inactive users) 

Note: Here the count of Active Users for Feb got reduced when compared against Jan and count of Inactive Users for Feb got increased when compared against Jan

Please let me know if the above explanation is not clear

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Users count active: = 
VAR _userstablerecentdate =
    SUMMARIZE (
        ADDCOLUMNS (
            SUMMARIZE (
                User,
                User[User ID],
                User[Active/Inactive Flag],
                User[Modified Timestamp]
            ),
            "@latestdate",
                CALCULATE (
                    MAXX (
                        FILTER ( User, User[Modified Timestamp] <= MAX ( 'Calendar'[Date] ) ),
                        User[Modified Timestamp]
                    )
                )
        ),
        User[User ID],
        [@latestdate]
    )
VAR _usercumulatetable =
    CALCULATETABLE (
        User,
        TREATAS ( _userstablerecentdate, User[User ID], User[Modified Timestamp] )
    )
RETURN
    COUNTROWS (
        FILTER ( _usercumulatetable, User[Active/Inactive Flag] = "Active" )
    )

Users count inactive: = 
VAR _userstablerecentdate =
    SUMMARIZE (
        ADDCOLUMNS (
            SUMMARIZE (
                User,
                User[User ID],
                User[Active/Inactive Flag],
                User[Modified Timestamp]
            ),
            "@latestdate",
                CALCULATE (
                    MAXX (
                        FILTER ( User, User[Modified Timestamp] <= MAX ( 'Calendar'[Date] ) ),
                        User[Modified Timestamp]
                    )
                )
        ),
        User[User ID],
        [@latestdate]
    )
VAR _usercumulatetable =
    CALCULATETABLE (
        User,
        TREATAS ( _userstablerecentdate, User[User ID], User[Modified Timestamp] )
    )
RETURN
    COUNTROWS (
        FILTER ( _usercumulatetable, User[Active/Inactive Flag] = "Inactive" )
    )